dynamic sql problem

Question

abhinav_8 0 Newbie Poster

8 Years Ago

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.

microsoft:sql-server

Edited 8 Years Ago by abhinav_8

4 Contributors
7 Replies
392 Views
6 Days Discussion Span
Latest Post 8 Years Ago Latest Post by abhinav_8

Dani AI

Generated 9 Months Ago

A few practical points to resolve the immediate syntax problem and make the dynamic SQL safe and maintainable.

Common causes: the assembled SQL is malformed (an unfinished WHERE or missing space so tokens run together), or duplicate/conflicting aliases inside nested subqueries. Verifying the exact statement the server parses is the fastest fix: print the full dynamically built SQL and run that text directly in SSMS to see the parser error location.

Checklist to follow

Build the object name with QUOTENAME(...) to avoid identifier/quoting issues and injection.
Use NVARCHAR and prefix literal SQL with N'...' when the string is Unicode.
Always include explicit spaces when concatenating (e.g., put a space before an alias or JOIN).
Use sp_executesql for parameterized filters (dates, ids) so those values are parameters rather than string-injected.
Print or SELECT the final SQL before executing so the exact text can be inspected.
Avoid reusing the same alias for different derived tables; give inner subqueries distinct short aliases.
If table names come from users, validate against a whitelist of allowed names.

Minimal safe pattern (generic)

DECLARE @tbl sysname = QUOTENAME(@optb);    -- safe object name
DECLARE @sql nvarchar(max);

SET @sql = N'
SELECT l.colA, o.colB
FROM ' + @tbl + N' AS o
JOIN dbo.Ledger AS l ON l.key = o.key
WHERE l.datecol BETWEEN @from AND @to;
';

DECLARE @params nvarchar(100) = N'@from date,@to date';
PRINT @sql;
EXEC sp_executesql @sql, @params, @from='2018-01-01', @to='2018-12-31';

Notes tied to thread: 's caution about dynamic SQL being a code smell is valid — if the only reason for dynamic SQL is swapping a year-specific table, consider a single table (or view/synonym) keyed by year instead. 's point about confusing alias reuse is also likely relevant. 's suggestion to post table DDL and sample rows will make any remaining diagnosis far quicker.

All 7 Replies

pty 882 Posting Pro

8 Years Ago

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 6,665 Hi, I'm Jim, one of DaniWeb's moderators.

8 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

abhinav_8 0 Newbie Poster · Answer 1 · 2018-08-14T03:42:53+00:00

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 24 Junior Poster in Training · Answer 2 · 2018-08-15T11:30:30+00:00

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 882 Posting Pro · Answer 3 · 2018-08-16T06:43:53+00:00

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 882 Posting Pro · Answer 4 · 2018-08-16T07:16:53+00:00

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 0 Newbie Poster · Answer 5 · 2018-08-18T03:24:41+00:00

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.

dynamic sql problem

Dani AI

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers