dynamic sql problem

[abhinav_8]

hi! I want to create a procedure which execute given below query using dynamic sql. i am getting some error i.e. "Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'GROUP'."
my query which i want to run by procedure is:

;with cte as (select *,t.opening+Recieve+returnback-Issue as balance from(SELECT p.trandate,p.voucherno,p.itemno,p.itemname,isnull(O.opening,0) opening,SUM(isnull(p.recieve,0))over(partition by p.itemno order by trandate )  Recieve,SUM(isnull(p.issue,0))over(partition by p.itemno order by trandate ) Issue,isnull(p.returnback,0) Returnback,ROW_NUMBER() OVER (Partition BY p.itemno Order by p.itemno,p.trandate) Row_Num ,O.opening as openingoriginal FROM (SELECT p.trandate,p.voucherno,p.itemno,p.itemname, SUM(isnull(p.recieve,0)) Recieve,SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback FROM ledgertable p  GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno) p LEFT JOIN  openingstock20172018 O ON O.itemno = p.itemno) t)select a.trandate,a.voucherno,a.itemno,a.itemname,case when b.balance is null then a.openingoriginal else b.balance end  as opening,c.recieve,c.issue,c.returnback,a.balance from  cte a  left join cte b on a.itemno=b.itemno and a.Row_Num=b.Row_Num+1 join (SELECT p.trandate,p.voucherno,p.itemno,p.itemname, SUM(isnull(p.recieve,0)) Recieve,SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback FROM ledgertable p GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno) c on c.itemno=a.itemno and a.trandate=c.trandate

and procedure which i have created is given below:

CREATE PROCEDURE GetLedger(@optb as varchar(50))

AS

BEGIN
declare @openingtable as varchar(1000) 
declare @query varchar(max)
 set @openingtable=@optb
set @query=N';with cte as (select *,t.opening+Recieve+returnback-Issue as balance from(SELECT p.trandate,p.voucherno,p.itemno,p.itemname,isnull(O.opening,0) opening,SUM(isnull(p.recieve,0))over(partition by p.itemno order by trandate )  Recieve,SUM(isnull(p.issue,0))over(partition by p.itemno order by trandate ) Issue,isnull(p.returnback,0) Returnback,ROW_NUMBER() OVER (Partition BY p.itemno Order by p.itemno,p.trandate) Row_Num ,O.opening as openingoriginal FROM (SELECT p.trandate,p.voucherno,p.itemno,p.itemname, SUM(isnull(p.recieve,0)) Recieve,SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback FROM ledgertable p where trandate between GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno) p LEFT JOIN'+@openingtable+'O ON O.itemno = p.itemno) t)select a.trandate,a.voucherno,a.itemno,a.itemname,case when b.balance is null then a.openingoriginal else b.balance end  as opening,c.recieve,c.issue,c.returnback,a.balance from  cte a  left join cte b on a.itemno=b.itemno and a.Row_Num=b.Row_Num+1 join (SELECT p.trandate,p.voucherno,p.itemno,p.itemname, SUM(isnull(p.recieve,0)) Recieve,SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback FROM ledgertable p GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno) c on c.itemno=a.itemno and a.trandate=c.trandate'
 execute(@query)
END

kindly help me out.

[pty]

I'll help you out by telling you that you need to format your code properly. I'm not surprised you can't find the error in that wall of text.

Lastly, dynamic SQL like this is what we'd call a 'code smell'. It's an indication that some other part of your design is bad, and by coming up with a harebrained and fragile mess like this is a symptom.

[Reverend Jim]

Aside from formatting, it might help if you describe in detail what you want your query to do and how your tables are set up.

[abhinav_8]

here is formatted code:

CREATE PROCEDURE GetLedger(@optb as varchar(50))
AS
BEGIN
declare @openingtable as varchar(1000) 
declare @query varchar(max)
 set @openingtable=@optb
set @query=N'

    ;with cte as (
    select *,t.opening+Recieve+returnback-Issue as balance 
    from(
           SELECT p.trandate,p.voucherno,p.itemno,p.itemname,isnull(O.opening,0) opening,
                   SUM(isnull(p.recieve,0))over(partition by p.itemno order by trandate )  Recieve,
                   SUM(isnull(p.issue,0))over(partition by p.itemno order by trandate ) Issue,
                   isnull(p.returnback,0) Returnback,
                   ROW_NUMBER() OVER (Partition BY p.itemno Order by p.itemno,p.trandate) Row_Num ,
                   O.opening as openingoriginal
           FROM ( 
                  SELECT p.trandate,p.voucherno,p.itemno,p.itemname, SUM(isnull(p.recieve,0)) Recieve,
                          SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback
                  FROM ledgertable p
                  GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno) p 
                  LEFT JOIN'+ @openingtable+ 'O ON O.itemno = p.itemno
                  ) t 
           )

    select a.trandate,a.voucherno,a.itemno,a.itemname,
            case when b.balance is null then a.openingoriginal else b.balance end  as opening,
            c.recieve,c.issue,c.returnback,a.balance
    from  cte a  left join cte b
    on a.itemno=b.itemno and a.Row_Num=b.Row_Num+1
    join 
           (SELECT p.trandate,p.voucherno,p.itemno,p.itemname, SUM(isnull(p.recieve,0)) Recieve,
                   SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback
           FROM ledgertable p
           GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno) c 
    on c.itemno=a.itemno and a.trandate=c.trandate'

[Smith5646]

I am guessing (since I don't have tables to verify with) that the problem is that in the below select you have two tables named p, the ledgertable and the result of the select. If I try this with MySQL, I get an error. If you find this is not the problem, I would recommend changing it anyhow because it looks really confusing.

( 
                  SELECT p.trandate,p.voucherno,p.itemno,p.itemname, SUM(isnull(p.recieve,0)) Recieve,
                          SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback
                  FROM ledgertable p
                  GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno) p 

[pty]

But why have different tables that have the same columns? This is a hacky solution toa hacky problem.

Add a key to your table and stop duplicating, everything else will get simpler.

[pty]

One day a column in one of the tables will be added, removed or renamed. What's going to break? Nobody knows. If you do this in one place there are undoubtedly other hacks, and they add up and form your technical debt. It's better to pay it off sooner rather than later.

[abhinav_8]

The above given query i am using to generate a ledger report. I am getting right output from this query but i want to pass a table name from a string in this query, so I want to use dynamic SQL query. I am doing any syntax mistake using dynamic SQL so kindly help me out in this.