2023年7月13日发(作者：)

SQLSERVER存储过程语法详解CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]

[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]@parameter

过程中的参数。在 Create PROCEDURE 语句中可以声明⼀个或多个参数。⽤户必须在执⾏过程时提供每个所声明参数的值（除⾮定义了该参数的默认值）。OUTPUT

表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使⽤ OUTPUT 参数可将信息返回给调⽤过程。Text、ntext 和 image 参数可⽤作 OUTPUT 参数。使⽤OUTPUT 关键字的输出参数可以是游标占位符。

AS :指定过程要执⾏的操作

SQLSERVER:变量的声明:声明变量时必须在变量前加@符号

DECLARE @I INT变量的赋值:变量赋值时变量前必须加setSET @I = 30声明多个变量:DECLARE @s varchar(10),@a INT

----------------------------------------------------------------------------------------oracle的建表sql转成sqlserver的建表sql时的注意点 :1.所有的comment语句需要删除。类型转换为text类型。类型转换为image类型。类型转换为int，number(16,2)等转换为decimal(16,2)，number(18)转换为bigint。t sysdate改为default getDate()。_date('2009-12-18','yyyy-mm-dd')改为cast('2009-12-18' as datetime)SQLSERVER:变量的声明:声明变量时必须在变量前加@符号DECLARE @I INT变量的赋值:变量赋值时变量前必须加setSET @I = 30声明多个变量:DECLARE @s varchar(10),@a INTif语句:Java代码

1. if .. 2. begin

3. ...

4. end

5. else if ..

6. begin

7. ...

8. end

9. else

10. begin

11. ...

12. end

Example:Sql代码

1. DECLARE @d INT

2. set @d = 1

3. IF @d = 1 BEGIN

4. PRINT '正确'

5. END

6. ELSE BEGIN

7. PRINT '错误'

8. END

多条件选择语句:Example:Sql代码

1. declare @today int

2. declare @week nvarchar(3)

3. set @today=3

4. set @week= case

5. when @today=1 then '星期⼀'

6. when @today=2 then '星期⼆'

7. when @today=3 then '星期三'

8. when @today=4 then '星期四'

9. when @today=5 then '星期五'

10. when @today=6 then '星期六'

11. when @today=7 then '星期⽇'

12. else '值错误'

13. end

14. print @week

循环语句:Java代码

1. WHILE 条件 BEGIN

2. 执⾏语句

3. END

Example:Java代码

1. DECLARE @i INT

2. SET @i = 1

3. WHILE @i<1000000 BEGIN

4. set @i=@i+1

5. END

定义游标：Sql代码

1. DECLARE @cur1 CURSOR FOR SELECT .........

2.

3. OPEN @cur1

4. FETCH NEXT FROM @cur1 INTO 变量 5. WHILE(@@FETCH_STATUS=0)

6. BEGIN

7. 处理.....

8. FETCH NEXT FROM @cur1 INTO 变量

9. END

10. CLOSE @cur1

11. DEALLOCATE @cur1

Sql代码

1. AS

2.

3. declare @CATEGORY_CI_TABLENAME VARCHAR(50) =''

4. declare @result VARCHAR(2000) = ''

5. declare @CI_ID DECIMAL = 0

6. declare @num int = 1

7. declare @countnum int = 1

8.

9. BEGIN

10. select @countnum = count(ATTRIBUTE_CONFIG_ID) from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE

11.

12. IF (@ATTRIBUTE2='A')

13. begin

14. DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGOR15. OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE

16. set @result = @result+@CONFIG_CODE+','

17. WHILE @@FETCH_STATUS = 0

18. BEGIN

19. FETCH NEXT FROM MyCursor INTO @CONFIG_CODE

20. set @num = @num+ 1

21. if(@num<@countnum)

22. begin

23. set @result = @result+@CONFIG_CODE+','

24. end

25. else if(@num=@countnum)

26. begin

27. set @result = @result +@CONFIG_CODE

28. end

29. END

30. CLOSE MyCursor

31. DEALLOCATE MyCursor

32. set @result = 'insert into ' + @ATTRIBUTE1 + '(' + @result +') select '+ @result +' from '+@CATEGORY_CI_TABLENAME +' where CI_ORDER_LINE_ID='+@KEY_ID

33. end

34. else if((@ATTRIBUTE2='U'))

临时表：-- Select INTO 从⼀个查询的计算结果中创建⼀个新表。 数据并不返回给客户端，这⼀点和普通的Select 不同。 新表的字段具有和 Select 的输出字段相关联（相同）的名字和数据类型。 select * into NewTable from Uname-- Insert INTO ABC Select -- 表ABC必须存在

-- 把表Uname⾥⾯的字段Username复制到表ABC Insert INTO ABC Select Username FROM Uname-- 创建临时表 Create TABLE #temp( UID int identity(1, 1) PRIMARY KEY, UserName varchar(16), Pwd varchar(50), Age smallint, Sex varchar(6) )

-- 打开临时表 Select * from #temp

1、局部临时表（#开头）只对当前连接有效，当前连接断开时⾃动删除。2、全局临时表（##开头）对其它连接也有效，在当前连接和其他访问过它的连接都断开时⾃动删除。3、不管局部临时表还是全局临时表，只要连接有访问权限，都可以⽤drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。临时表对执⾏效率应该影响不⼤，只要不是太过份，相反可以提⾼效率特别是连接查询的地⽅，只要你的数据库临时表空间⾜够游标多，会严重执⾏效率，能免则免！

===============================================================================其他：--有输⼊参数的存储过程--create proc GetComment(@commentid int)asselect * from Comment where CommentID=@commentid

--有输⼊与输出参数的存储过程--create proc GetCommentCount@newsid int,@count int outputasselect @count=count(*) from Comment where NewsID=@newsid

--返回单个值的函数--create function MyFunction(@newsid int)returns intasbegindeclare @count intselect @count=count(*) from Comment where NewsID=@newsidreturn @countend

--调⽤⽅法--declare @count intexec @count=MyFunction 2print @count

--返回值为表的函数--Create function GetFunctionTable(@newsid int)returns tableasreturn(select * from Comment where NewsID=@newsid)

--返回值为表的函数的调⽤--select * from GetFunctionTable(2)

-----------------------------------------------------------------------------------------------------------------------------------SQLServer 存储过程中不拼接SQL字符串实现多条件查询　以前拼接的写法　　set @sql=' select * from table where 1=1 '　　if (@addDate is not null)

　　 set @sql = @sql+' and addDate = '+ @addDate + ' '

　　if (@name <>'' and is not null)

　　 set @sql = @sql+ ' and name = ' + @name + ' '　　exec(@sql)下⾯是 不采⽤拼接SQL字符串实现多条件查询的解决⽅案　　第⼀种写法是 感觉代码有些冗余　　if (@addDate is not null) and (@name <> '')

　　 select * from table where addDate = @addDate and name = @name

　　else if (@addDate is not null) and (@name ='')

　　 select * from table where addDate = @addDate

　　else if(@addDate is null) and (@name <> '')

　　 select * from table where and name = @name

　　else if(@addDate is null) and (@name = '')

　　select * from table

　　第⼆种写法是

　　select * from table where (addDate = @addDate or @addDate is null) and (name = @name or @name = '')

　　第三种写法是

　　SELECT * FROM table where

　　addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END,

　　name = CASE @name WHEN '' THEN name ELSE @name END

-----------------------------------------------------------------------------------------------------------------------------------

SQLSERVER存储过程基本语法

⼀、定义变量--简单赋值declare @a intset @a=5print @a

--使⽤select语句赋值declare @user1 nvarchar(50)select @user1= '张三'print @user1declare @user2 nvarchar(50)select @user2 = Name from ST_User where ID=1print @user2

--使⽤update语句赋值declare @user3 nvarchar(50)update ST_User set @user3 = Name where ID=1print @user3

⼆、表、临时表、表变量--创建临时表1create table #DU_User1( [ID] [ int ] NOT NULL , [Oid] [ int ] NOT NULL , [Login] [nvarchar](50) NOT NULL , [Rtx] [nvarchar](4) NOT NULL , [ Name ] [nvarchar](5) NOT NULL , [ Password ] [nvarchar]( max ) NULL , [State] [nvarchar](8) NOT NULL);--向临时表1插⼊⼀条记录insert into #DU_User1 (ID,Oid,[Login],Rtx, Name ,[ Password ],State) values (100,2, 'LS' ,'0000' , '临时' , '321' , '特殊' );

--从ST_User查询数据，填充⾄新⽣成的临时表select * into #DU_User2 from ST_User where ID<8

--查询并联合两临时表select * from #DU_User2 where ID<3 union select * from #DU_User1

--删除两临时表drop table #DU_User1drop table #DU_User2 --创建临时表CREATE TABLE #t( [ID] [ int ] NOT NULL , [Oid] [ int ] NOT NULL , [Login] [nvarchar](50) NOT NULL , [Rtx] [nvarchar](4) NOT NULL , [ Name ] [nvarchar](5) NOT NULL , [ Password ] [nvarchar]( max ) NULL , [State] [nvarchar](8) NOT NULL ,)

--将查询结果集（多条数据）插⼊临时表insert into #t select * from ST_User--不能这样插⼊--select * into #t from _User

--添加⼀列，为int型⾃增长⼦段alter table #t add [myid] int NOT NULL IDENTITY(1,1)--添加⼀列，默认填充全球唯⼀标识alter table #t add [myid1] uniqueidentifier NOT NULL default (newid())

select * from #tdrop table #t--给查询结果集增加⾃增长列

--⽆主键时：select IDENTITY( int ,1,1) as ID, Name ,[Login],[ Password ] into #t from ST_Userselect * from #t

--有主键时：select ( select SUM (1) from ST_User where ID<= ) as myID,* from ST_User a order bymyID--定义表变量declare @t table( id int not null , msg nvarchar(50) null)insert into @t values (1, '1' )insert into @t values (2, '2' )select * from @t 三、循环--while循环计算1到100的和declare @a intdeclare @ sum intset @a=1set @ sum =0while @a<=100begin set @ sum +=@a set @a+=1endprint @ sum四、条件语句--if,else条件分⽀if(1+1=2)begin print '对'endelsebegin print '错'end

--when then条件分⽀declare @today intdeclare @week nvarchar(3)set @today=3set @week= case when @today=1 then '星期⼀' when @today=2 then '星期⼆' when @today=3 then '星期三' when @today=4 then '星期四' when @today=5 then '星期五' when @today=6 then '星期六' when @today=7 then '星期⽇' else '值错误'endprint @week

五、游标declare @ID intdeclare @Oid intdeclare @Login varchar (50)

--定义⼀个游标declare user_cur cursor for select ID,Oid,[Login] from ST_User--打开游标open user_curwhile @@fetch_status=0begin--读取游标 fetch next from user_cur into @ID,@Oid,@Login print @ID --print @Loginendclose user_cur--摧毁游标deallocate user_cur六、触发器　　 触发器中的临时表：　　Inserted

　　存放进⾏insert和update 操作后的数据

　　Deleted

　　存放进⾏delete 和update操作前的数据--创建触发器Create trigger User_OnUpdate

On ST_User

for Update

As

declare @msg nvarchar(50) --@msg记录修改情况 select @msg = N '姓名从“' + Deleted. Name + N '”修改为“' + Inserted. Name + '”' fromInserted,Deleted --插⼊⽇志表 insert into [LOG](MSG) values (@msg)

--删除触发器drop trigger User_OnUpdate七、存储过程--创建带output参数的存储过程CREATE PROCEDURE PR_Sum @a int , @b int , @ sum int outputASBEGIN set @ sum =@a+@bEND

--创建Return返回值存储过程CREATE PROCEDURE PR_Sum2 @a int , @b intASBEGIN Return @a+@bEND

--执⾏存储过程获取output型返回值declare @mysum intexecute PR_Sum 1,2,@mysum outputprint @mysum

--执⾏存储过程获取Return型返回值declare @mysum2 intexecute @mysum2= PR_Sum2 1,2print @mysum2

⼋、⾃定义函数　　函数的分类：　　　　1）标量值函数　　　　2）表值函数　　　　　　　　a:内联表值函数　　　　　　　　b:多语句表值函数　　　　3）系统函数

--新建标量值函数create function FUNC_Sum1( @a int , @b int)returns intasbegin return @a+@bend

--新建内联表值函数create function FUNC_UserTab_1( @myId int)returns tableasreturn ( select * from ST_User where ID<@myId)

--新建多语句表值函数create function FUNC_UserTab_2( @myId int)returns @t table( [ID] [ int ] NOT NULL , [Oid] [ int ] NOT NULL , [Login] [nvarchar](50) NOT NULL , [Rtx] [nvarchar](4) NOT NULL , [ Name ] [nvarchar](5) NOT NULL , [ Password ] [nvarchar]( max ) NULL , [State] [nvarchar](8) NOT NULL)asbegin insert into @t select * from ST_User where ID<@myId returnend

--调⽤表值函数select * from _UserTab_1(15)--调⽤标量值函数declare @s intset @s=_Sum1(100,50)print @s

--删除标量值函数drop function FUNC_Sum1谈谈⾃定义函数与存储过程的区别：⼀、⾃定义函数：　　1. 可以返回表变量　　2. 限制颇多，包括　　　　不能使⽤output参数；　　　　不能⽤临时表；　　　　函数内部的操作不能影响到外部环境；　　　　不能通过select返回结果集；　　　　不能update，delete，数据库表；　　3. 必须return ⼀个标量值或表变量　　⾃定义函数⼀般⽤在复⽤度⾼，功能简单单⼀，争对性强的地⽅。⼆、存储过程　　1. 不能返回表变量　　2. 限制少，可以执⾏对数据库表的操作，可以返回数据集　　3. 可以return⼀个标量值，也可以省略return　　　存储过程⼀般⽤在实现复杂的功能，数据操纵⽅⾯。

-----------------------------------------------------------------------------------------------------------------------------------SqlServer存储过程--实例实例1：只返回单⼀记录集的存储过程。　　表银⾏存款表（bankMoney）的内容如下

Id001002003userIDZhangsanWangwuZhangsanSex男男男Money305040

要求1：查询表bankMoney的内容的存储过程create procedure sp_query_bankMoneyasselect * from bankMoneygoexec sp_query_bankMoney注* 在使⽤过程中只需要把T-Sql中的SQL语句替换为存储过程名，就可以了很⽅便吧！实例2（向存储过程中传递参数）：加⼊⼀笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的总⾦额。Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int outputwith encryption ---------加密asinsert into bankMoney (id,userID,sex,Money)Values(@param1,@param2,@param3, @param4)select @param5=sum(Money) from bankMoney where userID='Zhangsan'go在SQL Server查询分析器中执⾏该存储过程的⽅法是：declare @total_price intexec insert_bank '004','Zhangsan','男',100,@total_price outputprint '总余额为'+convert(varchar,@total_price)go在这⾥再啰嗦⼀下存储过程的3种传回值（⽅便正在看这个例⼦的朋友不⽤再去查看语法内容）:1.以Return传回整数2.以output格式传回参数set传回值的区别:output和return都可在批次程式中⽤变量接收,⽽recordset则传回到执⾏批次的客户端中。实例3：使⽤带有复杂 SELECT 语句的简单过程　　下⾯的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使⽤任何参数。　　USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON _id = _id INNER JOIN titles t ON _id = _id INNER JOIN publishers p ON _id = _idGO　　au_info_all 存储过程可以通过以下⽅法执⾏：　　EXECUTE au_info_all-- OrEXEC au_info_all　　如果该过程是批处理中的第⼀条语句，则可使⽤：　　au_info_all实例4：使⽤带有参数的简单过程　　CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20)ASSELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON _id = _id INNER JOIN titles t ON _id = _id INNER JOIN publishers p ON _id = _id WHERE au_fname = @firstname AND au_lname = @lastnameGO　　au_info 存储过程可以通过以下⽅法执⾏：　　EXECUTE au_info 'Dull', 'Ann'-- OrEXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'-- OrEXEC au_info 'Dull', 'Ann'-- OrEXEC au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXEC au_info @firstname = 'Ann', @lastname = 'Dull'　　如果该过程是批处理中的第⼀条语句，则可使⽤：　　au_info 'Dull', 'Ann'-- Orau_info @lastname = 'Dull', @firstname = 'Ann'-- Orau_info @firstname = 'Ann', @lastname = 'Dull'

实例5：使⽤带有通配符参数的简单过程CREATE PROCEDURE au_info2@lastname varchar(30) = 'D%',@firstname varchar(18) = '%'ASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta ON _id = _id INNER JOIN titles t ON _id = _id INNER JOIN publishers p ON _id = _idWHERE au_fname LIKE @firstname AND au_lname LIKE @lastnameGO　　au_info2 存储过程可以⽤多种组合执⾏。下⾯只列出了部分组合：　　EXECUTE au_info2-- OrEXECUTE au_info2 'Wh%'-- OrEXECUTE au_info2 @firstname = 'A%'-- OrEXECUTE au_info2 '[CK]ars[OE]n'-- OrEXECUTE au_info2 'Hunter', 'Sheryl'-- OrEXECUTE au_info2 'H%', 'S%'　　= 'proc2'实例6：if...else存储过程，其中@case作为执⾏update的选择依据，⽤if...else实现执⾏时根据传⼊的参数执⾏不同的修改.

--下⾯是if……else的存储过程：

if exists (select 1 from sysobjects where name = 'Student' and type ='u' )drop table Studentgoif exists (select 1 from sysobjects where name = 'spUpdateStudent' and type ='p' )drop proc spUpdateStudentgocreate table Student(fName nvarchar (10),fAge smallint ,fDiqu varchar (50),fTel int

)goinsert into Student values ('X.X.Y' , 28, 'Tesing' , 888888)gocreate proc spUpdateStudent(@fCase int ,@fName nvarchar (10),@fAge smallint ,@fDiqu varchar (50),@fTel int

)as

update Studentset fAge = @fAge, -- 传 1,2,3 都要更新 fAge 不需要⽤ case

fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),fTel = (case when @fCase = 3 then @fTel else fTel end )where fName = @fNameselect * from Studentgo-- 只改 Age

exec spUpdateStudent@fCase = 1,@fName = N'X.X.Y' ,@fAge = 80,@fDiqu = N'Update' ,@fTel = 1010101-- 改 Age 和 Diqu

exec spUpdateStudent@fCase = 2,@fName = N'X.X.Y' ,@fAge = 80,@fDiqu = N'Update' ,@fTel = 1010101-- 全改

exec spUpdateStudent@fCase = 3,@fName = N'X.X.Y' ,@fAge = 80,@fDiqu = N'Update' ,@fTel = 1010101