一、普通带参数存储过程
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_getTotalRecords'))
drop proc proc_getTotalRecords
go
create proc proc_getTotalRecords
(@year int)
as
begin
declare @sqlWhere nvarchar(4000)='',
@sqlData nvarchar(4000)='';
if(@year>1975 and @year is not null)
begin
set @sqlWhere+='and DATEPART(YYYY,RECORD_TIME)= '+ convert(varchar, @year)
end
set @sqlData=' select DATEPART(MM,RECORD_TIME) name,count(*) value from 表名 where 1=1'
+@sqlWhere+
' group by DATEPART(MM,RECORD_TIME)'
EXEC SP_EXECUTESQL @sqlData
end
go
二、带输出参数+条件拼接的存储过程
if (exists (select * from sys.objects where name = 'proc_getTotalRecordsByUser'))
drop proc proc_getTotalRecordsByUser
go
create proc proc_getRecordsByUser
(@year int,@userId int,@code int output)
as
begin
declare @sqlWhere nvarchar(4000)='',
@sqlData nvarchar(4000)='';
if(@year>1975 and @year is not null)
begin
set @sqlWhere+='and DATEPART(YYYY,RECORD_TIME)= '+ convert(varchar, @year)
end
if(@userId>0 and @year is not null)
begin
if((select count(*) from XH_RECORD r where exists
(select * from SYS_USER u where r.USER_CODE=u.ID and r.USER_CODE=@userId))>0)
begin
SELECT @code=200
set @sqlWhere+='and USER_CODE= '+ convert(varchar, @userId)
end
else
begin
SELECT @code=-200
return;
end
end
set @sqlData=' select DATEPART(MM,RECORD_TIME) name,count(*) value from XH_RECORD where 1=1'
+@sqlWhere+
' group by DATEPART(MM,RECORD_TIME)'
EXEC SP_EXECUTESQL @sqlData
end