SQL 动态拼接语句及执行( sp_executesql)

Create proc [dbo].[GetHospitalList]

@pagesize int,
@page int,
@hospitalcode nvarchar(50),
@citycode nvarchar(50),
@type nvarchar(50),
@memo nvarchar(50),
@provincecode nvarchar(50),
@status nvarchar(50)
as
--sp_executesql 执行的参数@sql 需要是nvarchar类型。

declare @sql nvarchar(max)

set @sql='SELECT TOP '+(convert(nvarchar(10),@pagesize))+' totalcount, hospitalcode,NameCn ,
[status],CreateUser,CreateDate,UpdateDate ,Citycode,[type],memo from (
select TOP '+convert(nvarchar(10),(@page*@pagesize))+' ROW_NUMBER() OVER (ORDER BY namecn) AS RowNum ,count(1) over() as totalcount,
hospitalcode,NameCn , a.[status],CreateUser,CreateDate,UpdateDate ,
a.Citycode,[type],memo from table1 a 
left join table2 b on b.citycode=a.citycode where 1=1 '
if(@hospitalcode<>'')
begin
set @sql+=' and hospitalcode=@hospitalcode'
end
if(@citycode<>'')
begin
set @sql+=' and a.citycode=@citycode'
end
if(@type<>'')
begin
set @sql+=' and [type]=@type'
end
if(@memo<>'')
begin
set @sql+=' and memo=@memo'
end
if(@provincecode<>'')
begin
set @sql+=' and b.pcode=@provincecode'
end

set @sql+=' ) AS p WHERE RowNum BETWEEN '+Convert(nvarchar(10),((@page-1)*@pagesize+1))+' AND '+convert(nvarchar(10),(@page * @pagesize))+ ' ORDER BY RowNum'

print @sql

EXEC sp_executesql @sql ,N'@pagesize int,@page int, @hospitalcode nvarchar(50),@citycode nvarchar(50),@provincecode nvarchar(50),@memo nvarchar(50),@type nvarchar(50)',
@pagesize=@pagesize,@page=@page ,@hospitalcode=@hospitalcode,@citycode=@citycode,@provincecode=@provincecode,@memo=@memo,@type=@type