SQLServer存储过程类型及使⽤⽅法--有输⼊参数的存储过程--
create proc GetComment
(@commentid int)
as
select * from Comment where CommentID=@commentid
--有输⼊与输出参数的存储过程--
create proc GetCommentCount
@newsid int,
@count int output
as
select @count=count(*) from Comment where NewsID=@newsid
-
-返回单个值的函数--
create function MyFunction
(@newsid int)
returns int
as
begin
declare @count int
select @count=count(*) from Comment where NewsID=@newsid
return @count
end
--调⽤⽅法--
declare @count int
exec @count=MyFunction 2
print @count
--返回值为表的函数--
Create function GetFunctionTable
(@newsid int)
returns table
as
return
(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 int
set@a=5
print @a
--使⽤select语句赋值
declare@user1 nvarchar(50)
select@user1='张三'
sql存储过程实例print @user1
declare@user2 nvarchar(50)
select@user2 =Name fromST_User where ID=1 print @user2
--使⽤update语句赋值
declare@user3 nvarchar(50)
update ST_User set@user3 =Name where ID=1 print @user3
⼆、表、临时表、表变量
--创建临时表1
create table#DU_User1
(
[ID] [int] NOT NULL,
[Oid] [int]NOT NULL,
[Login] [nvarchar](50)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_User2from ST_User where ID<8
--查询并联合两临时表
select*from#DU_User2where ID<3union select*from#DU_User1
--删除两临时表
drop table#DU_User1
drop table#DU_User2
--创建临时表
CREATE TABLE#t
(
[ID] [int]NOT NULL,
[Oid] [int]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 dbo.ST_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#t
drop table#t
--给查询结果集增加⾃增长列
--⽆主键时：
select IDENTITY(int,1,1)as ID,Name,[Login],[Password]into#t fromST_User

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