SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Using dynamic SQL in SSRS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

itmaster
Starting Member

25 Posts

Posted - 08/07/2014 :  14:19:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1696 Posts

Posted - 08/07/2014 :  17:38:05  Show Profile  Reply with Quote
In order to have embedded single quotes in your string you need to use double single quotes, a la:
CASE WHEN RATYPE in (''8DI'',''8GI'',''8I'',''8X'',''8D'')
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

Edited by - Bustaz Kool on 08/07/2014 17:39:42
Go to Top of Page

itmaster
Starting Member

25 Posts

Posted - 08/08/2014 :  08:22:28  Show Profile  Reply with Quote
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:
CASE WHEN RATYPE in (''8DI'',''8GI'',''8I'',''8X'',''8D'')
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

Edited by - itmaster on 08/08/2014 08:23:41
Go to Top of Page

itmaster
Starting Member

25 Posts

Posted - 08/08/2014 :  08:42:58  Show Profile  Reply with Quote
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

Edited by - itmaster on 08/08/2014 08:47:02
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1696 Posts

Posted - 08/08/2014 :  11:33:26  Show Profile  Reply with Quote
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

Edited by - Bustaz Kool on 08/08/2014 11:38:29
Go to Top of Page

itmaster
Starting Member

25 Posts

Posted - 08/08/2014 :  12:32:47  Show Profile  Reply with Quote
Hi I got this to work thank you
Go to Top of Page

itmaster
Starting Member

25 Posts

Posted - 08/08/2014 :  12:33:45  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000