預存程序 Stored Procedure

Posted on Edited on In Views: Disqus:

 

資料準備

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE testTable
(
    [id] int,
    [name] varchar(10),
	[age] int,
);

INSERT INTO testTable ([id],[name],[age]) values (1,'Bill',40);
INSERT INTO testTable ([id],[name],[age]) values (2,'Tom',10);
INSERT INTO testTable ([id],[name],[age]) values (3,'Mary',60);
INSERT INTO testTable ([id],[name],[age]) values (4,'Joe',10);
INSERT INTO testTable ([id],[name],[age]) values (5,'Tim',60);
INSERT INTO testTable ([id],[name],[age]) values (6,'Alice',5);
id name age
1 Bill 40
2 Tom 10
3 Mary 60
4 Joe 10
5 Tim 60
6 Alice 5


一、創立預存程序

1
2
3
4
5
6
7
/*創建預存程序語法: CREATE PROCEDURE {程序名稱}*/
CREATE PROCEDURE dbo.spGetTable /*注意: 名稱不能是sp_開頭!("sp_"是預留給系統的))*/
AS
BEGIN
 /*從這邊開始輸入要預存的SQL指令*/
 SELECT * FROM testTable;
END


二、執行預存程序

1
EXEC dbo.spGetTable
id name age
1 Bill 40
2 Tom 10
3 Mary 60
4 Joe 10
5 Tim 60
6 Alice 5


三、變更預存程序

1
2
3
4
5
6
7
/*創建預存程序語法: CREATE PROCEDURE {程序名稱}*/
ALTER PROCEDURE dbo.spGetTable /*注意: 名稱不能是sp_開頭!("sp_"是預留給系統的))*/
AS
BEGIN
 /*從這邊開始輸入要預存的SQL指令*/
 SELECT * FROM testTable;
END


四、預存程序輸入參數

1
2
3
4
5
6
7
8
/*創建預存程序語法: CREATE PROCEDURE {程序名稱}*/
ALTER PROCEDURE dbo.spGetTable /*注意: 名稱不能是sp_開頭!("sp_"是預留給系統的))*/
@greater_age int
AS
BEGIN
 /*從這邊開始輸入要預存的SQL指令*/
 SELECT * FROM testTable where age > @greater_age;
END


五、預存程序輸入參數之執行

1
EXEC dbo.spGetTable 59
id name age
3 Mary 60
5 Tim 60

參考資料：
什麼是預存程序(Stored Procedure)?