动态sql

1.普通动态

set @sql='select count(*) from A'

exec  @sql

2.给变量赋值

  DECLARE @nOldDlyOrder INT,@execsql nVARCHAR(800)
  SET @execsql='SELECT  @nOldDlyOrder1=CopySdlyorder FROM dlyother where DlyOrder=4'   
  exec sp_executesql @execsql,N'@nOldDlyOrder1 int output ',@nOldDlyOrder output   
      print @nOldDlyOrder 

select @execsql='SELECT @nOldDlyOrder=CopySdlyorder FROM dlyother where DlyOrder=4'

EXEC (@execsql) --这种语句会报错 @nOldDlyOrder未定义

3.动态定义游标

    IF(@IsSaveCodeSL=0)
            BEGIN
                select @execsql='declare My_cursor1 cursor for '
                +' SELECT pggoodsidcodes.PtypeId,pggoodsidcodes.KtypeId,pggoodsidcodes.goodsorderid,pggoodsidcodes.SLtypeid,cltypeid,COUNT(1) pgholInqty,SUM(CAST(opgDetail AS NUMERIC(22,10)) ) qty FROM dbo.pggoodsidcodes WHERE  oldDlyOrder='+CAST(@nOldDlyOrder AS varchar(10)) +  '    GROUP BY   pggoodsidcodes.PtypeId,pggoodsidcodes.KtypeId,goodsorderid,SLtypeid,cltypeid '
                EXEC (@execsql)
            END
        ELSE
            BEGIN
                select @execsql='declare My_cursor1 cursor for '
                +' SELECT pggoodsidcodes.PtypeId,pggoodsidcodes.KtypeId,pggoodsidcodes.goodsorderid,pggoodsidcodes.SLtypeid,cltypeid,COUNT(1) pgholInqty,SUM(CAST(opgDetail AS NUMERIC(22,10)) ) qty FROM #pgStock LEFT join dbo.pggoodsidcodes ON #pgStock.lpgIDCode=pggoodsidcodes.pgIDCode GROUP BY   pggoodsidcodes.PtypeId,pggoodsidcodes.KtypeId,goodsorderid,SLtypeid,cltypeid  '
                EXEC (@execsql)
            END
            
            OPEN My_cursor1
                fetch next from My_cursor1 into @slPtypeId,@slKtypeId,@slgoodsorderid ,@slSLtypeid ,@slcltypeid ,@slpgholInqty ,@slqty 
                while @@FETCH_STATUS=0
                BEGIN
                        DELETE     FROM GoodsStocksCL WHERE Qty<=0                                                        
                        FETCH next from My_cursor1 into  @slPtypeId,@slKtypeId,@slgoodsorderid ,@slSLtypeid ,@slcltypeid ,@slpgholInqty ,@slqty 
                end    
                    CLOSE My_cursor1
                DEALLOCATE My_cursor1