自己總結(jié)的存儲(chǔ)過程學(xué)習(xí)筆記

1、存儲(chǔ)過程學(xué)習(xí)：1,設(shè)定參數(shù)可選sql語句：create procedure nameflag nvarchar(2)if (flag='1') select * from table_nameif (flag='2') select id from table_namereturn2.在存儲(chǔ)過程中引用另一個(gè)存儲(chǔ)過程的數(shù)據(jù)例12-3 在該存儲(chǔ)過程中使用了OUTPUT 保留字首先創(chuàng)建存儲(chǔ)過程use pubsgoif exists select name from sysobjectswhere name = 'titles_sum' a

2、nd type = 'p'drop procedure titles_sumgouse pubsgocreate procedure salequa stor_id char 4 ,sum smallint outputasselect ord_num, ord_date, payterms, title_id, qtyfrom saleswhere stor_id = stor_idselect sum = sum qtyfrom saleswhere stor_id = stor_idgo然后在Query Analyzer 中調(diào)用例12-3 的存儲(chǔ)過程declare tot

3、alqua smallintexecute salequa '7131',totalqua outputif totalqua<=50select '銷售信息'='銷售等級(jí)為3 銷售量為'+rtrim cast totalqua as varchar 20if totalqua>50 and totalqua<=100select '銷售信息'='銷售等級(jí)為2 銷售量為'+rtrim cast totalqua as varchar 20if totalqua>100select '

4、銷售信息'='銷售等級(jí)為1 銷售量為'+rtrim cast totalqua as varchar 20運(yùn)行結(jié)果為ord_num ord_date payterms title_id qty- - - - -N914008 1994-09-14 00:00:00.000 Net 30 PS2091 20N914014 1994-09-14 00:00:00.000 Net 30 MC3021 25P3087a 1993-05-29 00:00:00.000 Net 60 PS1372 20P3087a 1993-05-29 00:00:00.000 Net 60 PS

5、2106 25P3087a 1993-05-29 00:00:00.000 Net 60 PS3333 15P3087a 1993-05-29 00:00:00.000 Net 60 PS7777 256 row s affected銷售信息-銷售等級(jí)為1 銷售量為1301 row s affected管理存儲(chǔ)過程如果在創(chuàng)建存儲(chǔ)過程時(shí)使用了WITH ENCRYPTION 選項(xiàng)那么無論是使用Enterprise都是看不見的若增加OUTPUT 保留字到EXECUTE 語句中便可顯示返回參數(shù)result 的值OUTPUT要求參數(shù)值被作為一個(gè)變量傳送而不是作為一個(gè)常量如果我們提供三個(gè)字符串來執(zhí)行這一存儲(chǔ)過程我們將看不到字符串相加的結(jié)果雖然Select 語句用來對(duì)result 變量賦值但result 結(jié)果并沒有顯示再:create procedure strconnect str1 varchar(20) , str2 varchar(20) , connect varchar(40) outputasselect connect=str1 + str2returndeclare result varchar(40)execute strco