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<>'')beginset @sql+=' and hospitalcode=@hospitalcode'endif(@citycode<>'')beginset @sql+=' and a.citycode=@citycode'endif(@type<>'')beginset @sql+=' and [type]=@type'endif(@memo<>'')beginset @sql+=' and memo=@memo'endif(@provincecode<>'')beginset @sql+=' and b.pcode=@provincecode'endset @sql+=' ) AS p WHERE RowNum BETWEEN '+Convert(nvarchar(10),((@page-1)*@pagesize+1))+' AND '+convert(nvarchar(10),(@page * @pagesize))+ ' ORDER BY RowNum'print @sqlEXEC 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