存储过程动态拼接字符串

动态拼接SQL应用于有条件的SQL

ALTER proc [dbo].[P_APPLYBY_VENDOR]
    @filter varchar(2000), 
    @ApplyBy varchar(500),
    @StartRecord int,
	@MaxRecords int,
	@RowsCount int output
as
begin
set @RowsCount=0;

Create  table #t (
            RowID  bigint identity,
			VendorID uniqueidentifier, 
			NumCount int )
insert into #t
select ID,COUNT(ID) as NumCount from  A where A.PRFormID in  (select PRFormID from B where  UserID=''+@ApplyBy+'')  group by ID order by NumCount desc ;

declare @mySQL varchar(2000)=' select AC.* from  AC ';


set @mySQL=@mySQL+' inner join #t z ';

set @mySQL=@mySQL+' on AC.ID=z.ID and Ac.RowFlag!=-1 ';

Set @mySQL=@mySQL+' where RowID>'+CONVERT( varchar,@StartRecord)+' and RowID<='+CONVERT(varchar,@MaxRecords)+' ';

declare @mySQLRowCount nvarchar(4000);
set @mySQLRowCount=N'select @RowsCountxxx=COUNT(*) from  AC inner join #t z on AC.ID=z.ID  Where 1=1 ';


if(@filter is not null and @filter<>'')
  begin
    set @mySQL=@mySQL+@filter;
    set @mySQLRowCount=@mySQLRowCount+@filter;
  end
  
set @mySQL=@mySQL+' order by z.NumCount desc  ';


exec(@mySQL);

declare @ParmDefinition nvarchar(4000);
SET @ParmDefinition = N'@RowsCountxxx int OUTPUT';
EXEC sp_executesql @mySQLRowCount, @ParmDefinition,@RowsCountxxx=@RowsCount output;
select @RowsCount  RowsCount;

end