Using dynamic SQL in SSRS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Using dynamic SQL in SSRS

Author

Topic

itmaster
Starting Member

28 Posts

Posted - 2014-08-07 : 14:19:44

Hi,
I have made a basic sql and put it in SSRS, but it is comming back with na error "Line 9: Incorrect syntax near '8". There is no 8 at this point. However, I am not sure if I am entering the dynamic SQL right. For example normaly you would use single quotes,as I have it here, but that did not work; so I tryed double quotes; and I get the error above. With the single quotes i get "Line 32: Incorrect syntax near '8'.
Line 41: Incorrect syntax near ' +
'.
Line 43: Incorrect syntax near ' + "
Can someone please look at me code an tell me what I am doing wrong?

Thank you

Here is my code:

DECLARE @dt3 varchar(4)
DECLARE @dt varchar(2)
SET @dt = DATEPART(MONTH, GETDATE())

DECLARE @dt2 varchar (4)
SET @dt2 = DATEPART(YEAR, GETDATE())

IF LEN(@dt) < 2
BEGIN
    SET @dt = '0' + @dt 
END

SET @dt3 = RIGHT(@dt2,2) + @dt

DECLARE @TbHold varchar(8) 
SET @TbHold = 'RAP' + @dt3

DECLARE @strSQL nvarchar(2000) 
SET @strSQL = 'SELECT RACUST,
       RAACCT,
       RADATE, 
       RATIME, 
       RAOPHN, 
       RACITY, 
       RASTAT, 
       RATYPE, 
       RADUR, 
       RATREV, 
       RAREV, 
       RAAUTH, 
       BillName,
       CASE WHEN RATYPE in ('8DI','8GI','8I','8X','8D') 
           THEN raauth 
       ELSE RAPHN 
       END AS RAPHN,
       RADATETIME 
FROM '   

SET @strSQL = @strSQL + @TbHold + ' LEFT OUTER JOIN CallTypes ' +
' ON RATYPE = UsageName ' + 
' WHERE (RACUST = ' + @CUNO ' +
' OR RACUST IN (SELECT distinct CGCUST ' + 
               ' FROM TCPCGCM ' + 
               ' WHERE CGCPCU =  )) ' + @CUNO

PRINT @strSQL

EXEC Sp_executeSql @strSQL

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-07 : 17:38:05

In order to have embedded single quotes in your string you need to use double single quotes, a la:[CODE]CASE WHEN RATYPE in (''8DI'',''8GI'',''8I'',''8X'',''8D'')[/CODE]Also, I don't see where "@CUNO" is defined or initialized.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

itmaster
Starting Member

28 Posts

Posted - 2014-08-08 : 08:22:28

quote:
Originally posted by Bustaz Kool

In order to have embedded single quotes in your string you need to use double single quotes, a la:[CODE]CASE WHEN RATYPE in (''8DI'',''8GI'',''8I'',''8X'',''8D'')[/CODE]Also, I don't see where "@CUNO" is defined or initialized.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

I added the double quotes and I am still getting the following:
Line 42: Incorrect syntax near ' +
'.
Line 44: Incorrect syntax near ' +
'.
Unclosed quotation mark before the character string ' + @CUNO
Also,@CUNO is a input paramiter, and in other reports that I did while I had to set up a parameter out side the code, I did not declaere it in the code adn it worked. However, I did declare it now, but it did not work.
Any other ideas as to what is happing here would be great, I have trye a lot of things and nothing is working.
Thank you

itmaster
Starting Member

28 Posts

Posted - 2014-08-08 : 08:42:58

Now I am getting and I have no idea what it is telling me:
Syntax error converting the nvarchar value 'SELECT RACUST,
RAACCT,
RADATE,
RATIME,
RAOPHN,
RACITY,
RASTAT,
RATYPE,
RADUR,
RATREV,
RAREV,
RAAUTH,
BillName,
CASE WHEN RATYPE in ('8DI','8GI','8I','8X','8D')
THEN raauth
ELSE RAPHN
END AS RAPHN,
RA...
Here is my current code:


DECLARE @CUNO int
DECLARE @dt3 varchar(4)
DECLARE @dt varchar(2)
SET @dt = DATEPART(MONTH, GETDATE())

DECLARE @dt2 varchar (4)
SET @dt2 = DATEPART(YEAR, GETDATE())

IF LEN(@dt) < 2
BEGIN
    SET @dt = '0' + @dt 
END

SET @dt3 = RIGHT(@dt2,2) + @dt

DECLARE @TbHold varchar(8) 
SET @TbHold = 'RAP' + @dt3

DECLARE @strSQL nvarchar(2000) 
SET @strSQL = 'SELECT RACUST,
       RAACCT,
       RADATE, 
       RATIME, 
       RAOPHN, 
       RACITY, 
       RASTAT, 
       RATYPE, 
       RADUR, 
       RATREV, 
       RAREV, 
       RAAUTH, 
       BillName,
       CASE WHEN RATYPE in (''8DI'',''8GI'',''8I'',''8X'',''8D'') 
           THEN raauth 
       ELSE RAPHN 
       END AS RAPHN,
       RADATETIME 
FROM '   

SET @strSQL = @strSQL + @TbHold + ' LEFT OUTER JOIN CallTypes ' +
' ON RATYPE = UsageName ' + 
' WHERE (RACUST = ' + @CUNO  +
' OR RACUST IN (SELECT distinct CGCUST ' + 
               ' FROM TCPCGCM ' + 
               ' WHERE CGCPCU = ' + @CUNO +   ')) ' 

PRINT @strSQL

EXEC Sp_executeSql @strSQL

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-08 : 11:33:26

Ah! This is a common error. You are trying to implicitly make a string out of the @CUNO variable and concatenate it into @strSQL. It is not obvious but the rules for implicit conversion state that when a string and an integer are combined, it attempts to convert the string into an integer and perform arithmetic addition. This string, as you know, is not a number and the implicit conversion fails. The solution is to explicitly convert you integer into a string using: CAST(@CUNO as nvarchar(10))
Also, I see @CUNO as declared but no value assigned. If it is null, you entire concatenated string could evaluate to null. This depends on your SET ANSI_NULLS value.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

itmaster
Starting Member

28 Posts

Posted - 2014-08-08 : 12:32:47

Hi I got this to work thank you

itmaster
Starting Member

28 Posts

Posted - 2014-08-08 : 12:33:45

Thank you

quote:
Originally posted by Bustaz Kool

Ah! This is a common error. You are trying to implicitly make a string out of the @CUNO variable and concatenate it into @strSQL. It is not obvious but the rules for implicit conversion state that when a string and an integer are combined, it attempts to convert the string into an integer and perform arithmetic addition. This string, as you know, is not a number and the implicit conversion fails. The solution is to explicitly convert you integer into a string using: CAST(@CUNO as nvarchar(10))
Also, I see @CUNO as declared but no value assigned. If it is null, you entire concatenated string could evaluate to null. This depends on your SET ANSI_NULLS value.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources