sql procedure语句
1. 创建存储过程
```sql
CREATE PROCEDURE sp_create_table
AS
BEGIN
    CREATE TABLE dbo.Employee
    (
        ID INT PRIMARY KEY,
        Name NVARCHAR(50),
        Age INT,
        Department NVARCHAR(50)
    )
END
```
2. 查询存储过程
```sql
CREATE PROCEDURE sp_get_employee
AS
BEGIN
    SELECT * FROM dbo.Employee
END
```
3. 插入数据的存储过程
```sql
CREATE PROCEDURE sp_insert_employee
    @ID INT,
    @Name NVARCHAR(50),
    @Age INT,
    @Department NVARCHAR(50)
AS
BEGIN
    INSERT INTO dbo.Employee (ID, Name, Age, Department)
    VALUES (@ID, @Name, @Age, @Department)
END
```
4. 更新数据的存储过程
```sql
CREATE PROCEDURE sp_update_employee
    @ID INT,
    @Age INT
AS
BEGIN
    UPDATE dbo.Employee
    SET Age = @Age
    WHERE ID = @ID
END
```
5. 删除数据的存储过程
```sql
CREATE PROCEDURE sp_delete_employee
    @ID INT
AS
BEGIN
    DELETE FROM dbo.Employee
    WHERE ID = @ID
END
```
6. 带条件查询的存储过程
```sql
CREATE PROCEDURE sp_search_employee
    @Department NVARCHAR(50)
AS
BEGIN
    SELECT * FROM dbo.Employee
    WHERE Department = @Department
END
```
7. 带输出参数的存储过程
```sql
CREATE PROCEDURE sp_count_employee
    @Count INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) FROM dbo.Employee
END
```
8. 带事务的存储过程
```sql
CREATE PROCEDURE sp_update_employee_age
    @ID INT,
    @Age INT
AS
BEGIN
sql存储过程实例    BEGIN TRANSACTION
    BEGIN TRY
        UPDATE dbo.Employee
        SET Age = @Age
        WHERE ID = @ID
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
    END CATCH
END
```
9. 带循环的存储过程
```sql
CREATE PROCEDURE sp_increase_age
AS
BEGIN
    DECLARE @ID INT
    DECLARE @Age INT
    DECLARE cur CURSOR FOR
    SELECT ID, Age FROM dbo.Employee
    OPEN cur
    FETCH NEXT FROM cur INTO @ID, @Age
    WHILE @@FETCH_STATUS = 0

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。