# SQL Server 存储过程基本语法

> T-SQL 存储过程常用语法

---

## 一、变量定义

### 简单赋值

```sql
DECLARE @a INT
SET @a = 5
PRINT @a

-- 使用 SELECT 赋值
DECLARE @user1 NVARCHAR(50)
SELECT @user1 = '张三'
PRINT @user1
```

---

## 二、临时表

### 创建临时表

```sql
CREATE TABLE #TempTable
(
    [ID] INT NOT NULL,
    [Login] NVARCHAR(50) NOT NULL,
    [Name] NVARCHAR(50) NOT NULL
)

-- 插入数据
INSERT INTO #TempTable (ID, Login, Name) 
VALUES (100, 'LS', '临时')

-- 从查询创建
SELECT * INTO #TempTable2 FROM ST_User WHERE ID < 8

-- 删除
DROP TABLE #TempTable
```

### 表变量

```sql
DECLARE @t TABLE
(
    id INT NOT NULL,
    msg NVARCHAR(50) NULL
)

INSERT INTO @t VALUES (1, 'msg1')
INSERT INTO @t VALUES (2, 'msg2')

SELECT * FROM @t
```

---

## 三、IF/ELSE 条件

```sql
IF 条件
BEGIN
    -- 语句
END
ELSE IF 条件
BEGIN
    -- 语句
END
ELSE
BEGIN
    -- 语句
END
```

---

## 四、WHILE 循环

```sql
DECLARE @i INT = 1

WHILE @i <= 10
BEGIN
    PRINT @i
    SET @i = @i + 1
    
    -- 退出循环
    IF @i = 5
        BREAK
END
```

---

## 五、CASE 语句

```sql
SELECT 
    Name,
    CASE 
        WHEN Score >= 90 THEN '优秀'
        WHEN Score >= 60 THEN '及格'
        ELSE '不及格'
    END AS Grade
FROM Student
```

---

## 六、游标

```sql
DECLARE @id INT
DECLARE @name NVARCHAR(50)

DECLARE my_cursor CURSOR FOR
SELECT ID, Name FROM ST_User

OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CAST(@id AS NVARCHAR) + ' - ' + @name
    FETCH NEXT FROM my_cursor INTO @id, @name
END

CLOSE my_cursor
DEALLOCATE my_cursor
```

---

## 七、存储过程

### 创建存储过程

```sql
CREATE PROCEDURE sp_GetUser
    @UserID INT
AS
BEGIN
    SELECT * FROM ST_User WHERE ID = @UserID
END
```

### 执行存储过程

```sql
EXEC sp_GetUser @UserID = 1
```

### 带输出参数

```sql
CREATE PROCEDURE sp_CountUser
    @Count INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) FROM ST_User
END
```

---

## 八、常用函数

| 函数 | 说明 |
|------|------|
| `GETDATE()` | 当前日期时间 |
| `DATEADD()` | 日期加减 |
| `DATEDIFF()` | 日期间隔 |
| `ISNULL()` | 空值替换 |
| `CAST()` | 类型转换 |
| `LEN()` | 字符串长度 |