数据库
–有输入参数的存储过程–
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 (@