CODEWITHSUNDEEP
sql-server-2008dynamic-sql
Andy5
Andy5

Reputation: 2405

Dynamic SQL issue

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)

Upvotes: 0

Views: 1581

Answers (3)

Paul Williams
Paul Williams

Reputation: 17020

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.

Upvotes: 0

Taryn
Taryn

Reputation: 247640

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.

Upvotes: 1

Marc B
Marc B

Reputation: 360572

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

Upvotes: 0

Related Questions