Dynamic SQL issue

Question

I have a dynamic SQL which sits inside a stored procedure, but when I run the stored procedure I am not seeing any results. It is very odd, because when I strip out the SQL from the string, and just run it as an SQL Query I do get back results. I have tried getting the Dynamic SQL to print out so I could see what is going on, but this isn't working either. Therefore, I am at a loss to see what I am doing wrong, and would kindly ask if anyone can see what is wrong. Below is the query:

SELECT @SQL = @SQL + 'Select Production_Site, CSN, Target, Action, Fail '
SELECT @SQL = @SQL + 'From syn_products prod, '
SELECT @SQL = @SQL + '(select Production_Site, CSN, SUM([Target]) AS Target,SUM([Action]) AS Action,SUM([Fail]) AS Fail '
SELECT @SQL = @SQL + ' from '
SELECT @SQL = @SQL + ' ( '
SELECT @SQL = @SQL + ' select Production_Site, value, Period, YEAR, week, CSN '
SELECT @SQL = @SQL + ' from t_Pqe_Grocery '
SELECT @SQL = @SQL + ' unpivot ( '
SELECT @SQL = @SQL + ' value '
SELECT @SQL = @SQL + ' for col in (Grocery_Packaging_And_Coding, Grocery_Measurable, '
SELECT @SQL = @SQL + ' Grocery_Appearance, Grocery_Aroma, '
SELECT @SQL = @SQL + ' Grocery_Flavour, Grocery_Texture)) unp '
SELECT @SQL = @SQL + ' ) src '
SELECT @SQL = @SQL + ' pivot '
SELECT @SQL = @SQL + ' ( '
SELECT @SQL = @SQL + ' count(value) '
SELECT @SQL = @SQL + ' for value in ([Target], [Action], [Fail]) '
SELECT @SQL = @SQL + ' ) piv '
SELECT @SQL = @SQL + ' where Production_Site IN ( ''' + @Site + ''') AND YEAR BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToYear))+ 'AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromYear))+ 'AND Period BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToPeriod))+ ' AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromPeriod))+ 'AND Week BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToWeek))+ ' AND '+CONVERT(varchar(50),CONVERT(BIGINT,@FromWeek))+ ' GROUP BY Production_Site CSN'
SELECT @SQL = @SQL + ' ) pit'
SELECT @SQL = @SQL + ' WHERE prod.pProductCode = pit.CSN AND prod.pPowerBrand = ''POW'''
EXECUTE(@SQL)

Before your EXECUTE(@SQL), use either print @sql or select @sql to see what the query looks like, then try running that query — Taryn
– Taryn, Commented Nov 30, 2012 at 14:29
As I said in my note - I did that and it showed nothing to me — Andy5
– Andy5, Commented Nov 30, 2012 at 14:33
It shows you nothing from the stored procedure? If so, then try running it outside of the stored proc, just open a new query window and execute only this part to get the query string — Taryn
– Taryn, Commented Nov 30, 2012 at 14:34
And do you get any errors? Can you see the query string? If you can see the string, edit your OP and post the query that you are getting. — Taryn
– Taryn, Commented Nov 30, 2012 at 14:38
I am not getting any error and the print sql is not showing either. I have tried breaking the query up without any restrictions, and that didn't work either — Andy5
– Andy5, Commented Nov 30, 2012 at 14:49

Taryn · Accepted Answer · 2012-11-30 15:05:32Z

Sometimes formatting your query in a different way can help find any errors with your query. You were missing some spaces in your query string:

declare @sql varchar(max)
declare @Site varchar(10) = 'testSite'
declare @ToYear int = 2010
declare @FromYear int = 2012
declare @ToPeriod int = 45
declare @FromPeriod int = 56
declare @ToWeek int = 10
declare @FromWeek  int = 1

SET @SQL =
 'Select Production_Site, CSN, Target, Action, Fail 
  From syn_products prod
  inner join 
  (
    select Production_Site, CSN, SUM([Target]) AS Target,SUM([Action]) AS Action,SUM([Fail]) AS Fail 
    from 
    ( 
      select Production_Site, value, Period, YEAR, week, CSN 
      from t_Pqe_Grocery 
      unpivot 
      ( 
        value 
        for col in (Grocery_Packaging_And_Coding, 
                    Grocery_Measurable, Grocery_Appearance, 
                    Grocery_Aroma, Grocery_Flavour, Grocery_Texture)
      ) unp 
    ) src 
    pivot
    ( 
      count(value)
      for value in ([Target], [Action], [Fail])
    ) piv 
    where Production_Site IN ( ''' + @Site + ''') 
      AND YEAR BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToYear))+ ' AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromYear))
      + ' AND Period BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToPeriod))+ ' AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromPeriod)) 
      + ' AND Week BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToWeek))+ ' AND '+CONVERT(varchar(50),CONVERT(BIGINT,@FromWeek))
    + ' GROUP BY Production_Site CSN
  ) pit
     on prod.pProductCode = pit.CSN 
  where prod.pPowerBrand = ''POW'''

select @sql

This is now printing --- See SQL Fiddle with Demo -- I also changed the query to use ANSI join syntax instead of comma separated joins.

Did you alter it? When I load the fiddle I get no errors -- sqlfiddle.com/#!3/d41d8/6861

Marc B · Accepted Answer · 2012-11-30 14:28:04Z

0

These are probably syntax errors:

... CONVERT(BIGINT,@ToYear))+ 'AND '+ ...
                               ^--- no space

... @FromYear))+ 'AND Period BETWEEN ...
                  ^---no space

... @FromPeriod))+ 'AND Week BETWEEN
                    ^-- yet again no space

answered Nov 30, 2012 at 14:28

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

Marc B Over a year ago

yes, that's the problem. you'll be generating something like ... 2012AND

Andy5 Over a year ago

Sorry, mis-understood you. Add spaces still no change

Paul Williams · Accepted Answer · 2012-11-30 15:30:29Z

One of your variables is probably NULL. Concatenating a NULL value into your string will result in a NULL string. Both PRINT and EXECUTE when given NULL strings..

First, you need to set the @SQL parameter to an empty string or change the first line to set the value instead of concatenating it. Then, you may need to do some kind of checking to verify the parameters are NOT NULL and, if they are, either remove the criteria, or substitute something else:

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ''
SELECT @SQL = @SQL + ... -- now build the SQL Statement
SELECT @SQL = @SQL + ' where Production_Site IN ( ''' + ISNULL(@Site, '') + ''' ... -- check for NULLs here

PRINT ISNULL(@SQL, 'NULL) -- this should now print something even if the SQL is NULL

Finally, beware of SQL injection attacks! Avoid concatenating parameters into a dynamic SQL statement like this. Instead, parameterize the dynamic SQL, and pass the parameters along with the EXECUTE statement.

Collectives™ on Stack Overflow

Dynamic SQL issue

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related