none
Query Running Independently but not as Dynamic Query RRS feed

  • Question

  • So I'm fairly new to SQL and i am trying to run a query as parameterized dynamic query. The code was running when it is run independently but is presenting errors when I run it as dynamic query.

    THE CODE

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- ALTER PROCEDURE [dbo].[spPurGrnPret]
    -- (@cond int,@database NVARCHAR(MAX))
    -- As
    begin
    ----------------------Temporary Tables Droped---------------------
    
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CPO' AND type = 'U')DROP TABLE #CPO  
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CPoNo' AND type = 'U')DROP TABLE #CPoNo
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CPoGrn1' AND type = 'U')DROP TABLE #CPoGrn1
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CPoNo' AND type = 'U')DROP TABLE #CPoNo
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CPRet' AND type = 'U')DROP TABLE #CPRet
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CPoGrn' AND type = 'U')DROP TABLE #CPoGrn
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CPoPnd' AND type = 'U')DROP TABLE #CPoPnd
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CTPoDel' AND type = 'U')DROP TABLE #CTPoDel
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#PurGrnPret' AND type = 'U')drop table  #PurGrnPret
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#cPODet' AND type = 'U')drop table  #cPODet
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#cRcvdGrnwise' AND type = 'U')drop table  #cRcvdGrnwise
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#cTotRet' AND type = 'U')drop table  #cTotRet
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CGRNPret' AND type = 'U')drop table  #CGRNPret
    IF EXISTS(SELECT name FROM sysobjects WHERE NAME='#CGRNPretSum' AND type = 'U')drop table  #CGRNPretSum
    
    DECLARE @database NVARCHAR(MAX) 
    SET @database = 'AAHOTX16'
    DECLARE @SQL NVARCHAR(MAX)
    declare @cond int
    set @cond = 2
    
    select @SQL = 'use ' + @database + ';
    select ltrim(rtrim(Pur.yr))+''/''+ltrim(rtrim(str(Pur.po_no))) as PONO,convert(varchar(10), Pur.po_date, 103) as po_date,
    		Pur.vendor, convert(varchar(10), Pur.del_dt, 103) as del_dt, convert(varchar(10), Pur.remind_dt, 103) as remind_dt,
    		convert(varchar(10), Pur.cancel_dt, 103) as cancel_dt, pi.item_cat, pi.item_id, pi.SIZEZ, pi.color,
    		sum(pi.recd_qty - (pi.canc_qty * case when (pi.qty_conv >0 ) then  pi.qty_conv else 1 End)) as qty,
    		pi.price as rate,Pur.l_currency,pi.item_unit as pur_unit,pi.ord_no as job_no, pi.fitem_id as style,
    		pi.item_cat+pi.item_id+pi.sizez+pi.color as item ,PUR.CMP
    into #CPO 	from  Pi inner join Pur on Pi.sl_no = Pur.sl_no		
    		group by Pur.yr,Pur.po_no,Pur.po_date,Pur.vendor,Pur.del_dt,Pur.remind_dt,	Pur.cancel_dt,pi.item_cat,pi.item_id, pi.SIZEZ, 
    		pi.color,pi.ord_no,pi.fitem_id,	pi.price,Pur.l_currency,pi.item_unit ,PUR.CMP
    		order by del_dt,pi.item_cat,pi.item_id,pi.SIZEZ,pi.color
    select distinct pono into #CPoNo  from #CPO where pono <> '' 
    select Grn.po_no, ltrim(rtrim(Grn.yr))+''/''+ltrim(rtrim(str(Grn.NOZ))) as grnno,
    	convert(varchar(10), Grn.DATEZ, 103) as Grndate,Grn.party_chln, Grn_c.item_cat, Grn_c.item_id,Grn_c.SIZEZ,	Grn_c.color, 
    	sum(Grn_c.receipt) as Grnqty,Grn_c.pur_unit,Grn_c.bill_no as grnbill,Grn_c.sl_no,grn_c.job_no,grn_c.style,	
    	Grn_c.item_cat+Grn_c.item_id+Grn_c.sizez+Grn_c.color as item
    INTO #CPoGrn1 from #CPoNo inner join Grn inner join Grn_c
    	on Grn.sl_no = Grn_c.sl_no	on #CPoNo.pono = Grn.po_no
    	group by Grn_c.sl_no,Grn_c.item_cat,Grn_c.item_id,Grn_c.SIZEZ,Grn_c.color, grn_c.job_no,grn_c.style,Grn.PO_NO,
    	Grn.YR,Grn.NOZ,Grn.DATEZ,Grn.PARTY_CHLN,Grn_c.PUR_UNIT,Grn_c.BILL_NO
    select ltrim(rtrim(Pret.yr))+''/''+ltrim(rtrim(str(Pret.NOZ))) as pretno, convert(varchar(10), Pret.DATEZ, 103) as  Pretdate,
    	Pret.grn_no, Pret_c.item_cat, Pret_c.item_id, Pret_c.SIZEZ, Pret_c.color,
    	sum(Pret_c.issue) as pretqty,'' as pretbill,pret_c.job_no,pret_c.style, 
    	Pret_c.item_cat+Pret_c.item_id+Pret_c.sizez+Pret_c.color as item 
    INTO #CPRet 	
    	from Pret inner join Pret_c 
    	on  Pret.sl_no = Pret_c.sl_no 
    	where ltrim(rtrim(Pret.grn_no)) in (select distinct ltrim(rtrim(#CPoGrn1.grnno)) from #CPoGrn1)
    	group by Pret.yr,Pret.NOZ, Pret.DATEZ, Pret.grn_no, Pret_c.item_cat,
    	Pret_c.item_id, Pret_c.SIZEZ,pret_c.color,pret_c.job_no, pret_c.style
    select #CPoGrn1.item_cat,#CPoGrn1.item_id,#CPoGrn1.SIZEZ,#CPoGrn1.color,po_no,grnno, grndate,party_chln, 
    	sum(grnqty) as grnqty, pur_unit,grnbill,	isnull(pretno,'')as pretno, isnull(pretdate,'') as pretdate,
    	sum(isnull(pretqty,0)) as pretqty,isnull(pretbill,'') as pretbill, 	#CPoGrn1.job_no,#CPoGrn1.style,#CPoGrn1.item ,#CPoGrn1.sl_no
    INTO #CPoGrn from  #CPoGrn1 left outer join #CPRet
    	on ltrim(rtrim(#CPoGrn1.grnno)) + #CPoGrn1.item + str(#CPoGrn1.job_no) + #CPoGrn1.style = 
    	ltrim(rtrim(#CPRet.grn_no))+#CPRet.item+ str(#CPRet.job_no)+#CPRet.style
    	group by #CPoGrn1.po_no,#CPoGrn1.item_cat, #CPoGrn1.item_id, #CPoGrn1.SIZEZ,po_no,grnno,grndate,party_chln,grnqty,pur_unit,grnbill,
    	#CPoGrn1.sl_no,#CPoGrn1.color,#CPoGrn1.job_no,#CPoGrn1.style,#CPoGrn1.item ,pretno,pretdate,pretbill
    
    select #CPO.*,isnull(#CPoGrn.grnno,'') as grnno,	isnull(#CPoGrn.grndate,'') as grndate,
    	isnull(#CPoGrn.party_chln,'') as party_chln, isnull(grnqty,0) as grnqty, isnull(grnbill,'') as grnbill, 
    	isnull(pretno,'') as pretno,isnull(pretdate,'') as pretdate, isnull(pretqty,0) as pretqty,
    	isnull(#CPoGrn.pretbill,'') as pretbill 
    into #CPoPnd from  #CPO left outer join #CPoGrn 
    	on  ltrim(#CPO.pono)+ #CPO.item+str(#CPO.job_no) + #CPO.style = 
    	ltrim(rtrim(#CPoGrn.po_no))+#CPoGrn.item + 	str(#CPoGrn.job_no)+#CPoGrn.style 
    
    select #CPoPnd.*,isnull(item.Item_name,'') as Item_name,
    			isnull(item.descript,'') as descript,ITEM.UNIT AS PUR_UNITS
    INTO #CTPoDel 	from #CPoPnd left outer join item 
    			on #CPoPnd.item_cat+#CPoPnd.item_id = item.item_cat + item.item_id 
    			where (#CPoPnd.qty - (#CPoPnd.grnqty - #CPoPnd.pretqty)) > 0 
    			order by del_dt,#CPoPnd.item_cat,vendor,pono,#CPoPnd.item_id,
    			#CPoPnd.SIZEZ, #CPoPnd.color 
    
    select DISTINCT #CTPoDel.*,sizemast.ord				
    	into #PurGrnPret	from #CTPoDel left outer join sizemast
    		on 	#CTPoDel.SIZEZ = sizemast.SIZEZ
    			order by ord 
    
    If (@cond =1)
    	Begin
    		select * from #PurGrnPret
    	end			
    Else			
    	Begin  
    		select PONO,po_date, vendor,del_dt ,remind_dt ,	cancel_dt,item_cat ,item_id , SIZEZ ,color  ,l_currency ,pur_unit, 
    					job_no  ,Style ,item , Item_name,  qty , rate, cmp
    		into #cPODet  from #PurGrnPret	
    			group by PONO,vendor,del_dt ,remind_dt , rate ,qty ,cancel_dt,item_cat ,item_id , SIZEZ ,color  ,l_currency ,pur_unit, 
    					job_no  ,Style ,item , Item_name,cmp,po_date
    			order by del_dt, vendor, job_no, style
    select PONO,grnno,item_cat ,item_id , SIZEZ ,color, job_no, Style ,item, sum(grnqty) as totrcvd
    	into #cRcvdGrnwise from #PurGrnPret	
    	group by PONO,grnno,item_cat ,item_id , SIZEZ ,color, job_no, Style ,item
    
    select grnno,item_cat ,item_id , SIZEZ ,color, job_no, Style ,item, sum(pretqty) as totret
    	into #cTotRet from #PurGrnPret	
    	group by grnno,item_cat ,item_id , SIZEZ ,color, job_no, Style ,item
    Select #cRcvdGrnwise.PONO,#cRcvdGrnwise.grnno,#cRcvdGrnwise.item_cat ,#cRcvdGrnwise.item_id , #cRcvdGrnwise.SIZEZ ,#cRcvdGrnwise.color,
    	#cRcvdGrnwise.job_no, #cRcvdGrnwise.Style ,#cRcvdGrnwise.item, #cRcvdGrnwise.totrcvd, #cTotRet.totret
    	into #CGRNPret  	from #cRcvdGrnwise inner join  #cTotRet 
    		on ltrim(#cRcvdGrnwise.grnno)+''+ltrim(#cRcvdGrnwise.item_cat)+''+ltrim(#cRcvdGrnwise.item_id)+''+ltrim(#cRcvdGrnwise.SIZEZ)+''+ltrim(#cRcvdGrnwise.color)+''+ltrim(#cRcvdGrnwise.job_no)+''+ltrim(#cRcvdGrnwise.Style) =
    		ltrim(#cTotRet.grnno)+''+ltrim(#cTotRet.item_cat)+''+ltrim(#cTotRet.item_id)+''+ltrim(#cTotRet.SIZEZ)+''+ltrim(#cTotRet.color)+''+ltrim(#cTotRet.job_no)+''+ltrim(#cTotRet.Style) 
    Select PONO,item_cat ,item_id , SIZEZ ,color, job_no, Style ,item, sum(totrcvd) as totrcv, sum(totret) as totpret
    	into #CGRNPretSum  	from #CGRNPret
    		group by pono,item_cat ,item_id , SIZEZ ,color, job_no, Style ,item
    	
    Select #cPODet.*, totrcv, totpret
    	 from #cPODet inner join #CGRNPretSum
    	on ltrim(#cPODet.pono)+''+ltrim(#cPODet.item_cat)+''+ltrim(#cPODet.item_id)+''+ltrim(#cPODet.SIZEZ)+''+ltrim(#cPODet.color)+''+ltrim(#cPODet.job_no)+''+ltrim(#cPODet.Style)+''+ltrim(#cPODet.item) =
    	 ltrim(#CGRNPretSum.pono)+''+ltrim(#CGRNPretSum.item_cat)+''+ltrim(#CGRNPretSum.item_id)+''+ltrim(#CGRNPretSum.SIZEZ)+''+ltrim(#CGRNPretSum.color)+''+ltrim(#CGRNPretSum.job_no)+''+ltrim(#CGRNPretSum.Style)+''+ltrim(#CGRNPretSum.item)
    end'
    			
    				
    ----------------------Temporary Tables Droped---------------------
    EXEC sp_executesql @sql,
                          N' @cond INT , @database VARCHAR(10)',
                             @database, @cond
    
    
    
    
    
    --exec Podel 'F','9/1/2013','9/30/2013','0'
    
    END
    
    GO


    ERROR RECEIVED ARE 

    Started executing query at Line 4
    Msg 156, Level 15, State 1, Line 26
    Incorrect syntax near the keyword 'as'.
    Msg 156, Level 15, State 1, Line 32
    Incorrect syntax near the keyword 'group'.

    How can I debug the issue, I can't see a problem with the syntax.

    Any help would be appreciated.

    Thanks in advance


    • Edited by aviral91 Saturday, August 24, 2019 8:58 AM
    Saturday, August 24, 2019 8:57 AM

All replies

  • Hi

    before the second group by you have two 'on'

    on Grn.sl_no = Grn_c.sl_no on #CPoNo.pono = Grn.po_no

    check if that is not what causes the problem

    Regards;

    Saturday, August 24, 2019 9:27 AM
  • Duplicate on SO. It is rude and wasteful to post the same question to multiple forums independently. How to debug? You break that giant string into pieces to help find the problem. And learn to use formatting to make it readable. 
    Saturday, August 24, 2019 12:35 PM
  • As far as my understanding goes, the audience of both these forums could be different and maybe someone on this forum could help solve my problem. So in all fairness, I don't think it's rude. Please let me if it is otherwise, so I can avoid such a mistake in the future.

    Thanks

    Sunday, August 25, 2019 11:35 AM
  • No this wasn't the problem. Thanks for going through the code though.
    Sunday, August 25, 2019 11:37 AM
  • As far as my understanding goes, the audience of both these forums could be different and maybe someone on this forum could help solve my problem. So in all fairness, I don't think it's rude. Please let me if it is otherwise, so I can avoid such a mistake in the future.

    Thanks

    Good day avira,

    First, I totally agree with you!

    As I see it, posting in two or more different communities is totally OK...
    As long as you: (1) Provide links to other threads, assuming the communities rules allow to add external link and if not so mention the fact you opened in another place, so people could check what was already discuss there. (2) follow any thread which you open and respond to responses you get in ALL THREADS in all communities. (3) You remember to update and sync information between threads - for example if you got answer in one place or even a good tip which can help to understand and get the reader to the right answer, then you must update the information in all other threads, so no one will waste his time on something which already was done in another thread.

    You explain exactly why this is OK😃
    the audience of both these forums could be different

    For example, I hate stackoverflow interface and almost do not go there at all. If you did not publish here, then I could not know about this issue... and I am not the only one, and this true for both direction. There are people at stackoverflow which do not come to the MSDN forums.

    Just please keep the three points I mentioned under "As long as you"

    ====== Back to the question ======

    You mentioned that you have a working query and the only issue is with converting it to dynamic query.

    In this case, please provide the simple version of the (not-dynamic) query. Maybe we can start from the beginning and help you with tips on how to convert complex queries to dynamic queries.

    In the meantime, make sure that you read all the comments here and in any other thread which you cretaed


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, August 26, 2019 12:20 AM
    Moderator
  • Hi aviral91,

     

     Firstly, Single quotes are escaped by doubling them up, just as you've shown us in your example. The following SQL illustrates this functionality. I tested it successfully.

     

    DECLARE @my_table TABLE (
        [value] VARCHAR(200))
    INSERT INTO @my_table VALUES ('hi, my name''s tim.')
    SELECT * FROM @my_table
    /*
    value
    --------------------
    hi, my name's tim.
    */


     

    I think that might be your problem. Please check your script .

     

    In your script , I noticed several places. Please check other parts.

     
    ----select distinct pono into #CPoNo  from #CPO where pono <> ''
    select distinct pono into #CPoNo  from #CPO where pono <> ''''
    
    ----''+ltrim(#cRcvdGrnwise.item_cat)+''
    ''''+ltrim(#cRcvdGrnwise.item_cat)+''''
    
    ----isnull(#CPoGrn.grndate,'')
    isnull(#CPoGrn.grndate,'''')
    
     


    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, August 26, 2019 6:46 AM