May 16, 2013 at 10:57 am
Okay, I have created an application where you can add what we call (Export Scripts) and when you run the script, it creates an Excel spreadsheet with the result set.
I have already created about 26 scripts all running fine. We have multiple databases so we use a variable called say DBNAME and we have different amounts of parameters that a script may have.
I'm having trouble trying to figure out why this (because others work) will not work. I guess it may be something to do with the IF Statements.
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @ValueType Varchar(5)
DECLARE @Land_Bldg_Both Varchar(5)
SET @ValueType = 'RES'
SET @Land_Bldg_Both = 'BOTH'
DECLARE @MY_STRING varchar(3000)
SET @MY_STRING = 'DECLARE @ValueType varchar(5)
DECLARE @Land_Bldg_Both varchar(5)
SET @ValueType = ' + @ValueType + '
SET @Land_Bldg_Both = ' + @Land_Bldg_Both + '
IF @ValueType = "RES"
BEGIN
IF @Land_Bldg_Both = "BOTH"
BEGIN
SELECT COUNT(cMajor) As [Residential Count]
FROM NDTAX.dbo.TXPAR
WHERE nResidenceLot + nResidenceStruc >= 20000
AND nResidenceLot + nResidenceStruc <= 140000
AND cYear = 2012
END
IF @Land_Bldg_Both = "LAND"
BEGIN
SELECT COUNT(cMajor) As [Res Land Count]
FROM NDTAX.dbo.TXPAR
WHERE nResidenceLot >= 20000
AND nResidenceLot <= 140000
AND cYear = 2012
END
IF @Land_Bldg_Both = "BLDG"
BEGIN
SELECT COUNT(cMajor) As [Bldg Count]
FROM NDTAX.dbo.TXPAR
WHERE nResidenceStruc >= 20000
AND nResidenceStruc <= 140000
AND cYear = 2012
END
END'
EXEC(@MY_STRING);
GO
I have tried the code different ways, my main error I get is
Msg 207, Level 16, State 1, Line 4
Invalid column name 'RES'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'BOTH'.
SQL Server 2008 R2
May 16, 2013 at 11:04 am
I could be wrong, but it does appear that you have enclosed your RES value in double quotes " inside of your @MY_STRING sql string. If so, you should change it to two-single-quotes : ''RES'':
'
''RES''
'
instead of
'
"RES"
'
The same goes for all other strings enclosed in double-quotes inside of dynamic sql
May 16, 2013 at 11:05 am
If you print out the string, you'll notice that the SETs look like:
SET @ValueType = RES
SET @Land_Bldg_Both = BOTH
You'll need to add the quotes around the constants in order for the syntax to be valid.
It's always a good idea to print out the strings to validate the syntax.
May 16, 2013 at 11:05 am
You have a couple syntax issues in your dynamic sql.
First instead of using double quotes " you should use 2 single quotes ''.
The bigger issue is that you have not set your values correctly.
You have this:
SET @MY_STRING = 'DECLARE @ValueType varchar(5)
DECLARE @Land_Bldg_Both varchar(5)
SET @ValueType = ' + @ValueType + '
SET @Land_Bldg_Both = ' + @Land_Bldg_Both + '
If you select your string at that point it is very obvious what is wrong.
You should instead have it like this:
SET @MY_STRING = 'DECLARE @ValueType varchar(5)
DECLARE @Land_Bldg_Both varchar(5)
SET @ValueType = ''' + @ValueType + '''
SET @Land_Bldg_Both = ''' + @Land_Bldg_Both + '''
Hope that helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2013 at 11:19 am
To Crazy And CrazyEights.....
I don't think that I need to do that because if you noticed the command
SET QUOTED_IDENTIFIER OFF
GO
This treats all "" as '' ... as for the other guy I forgot what he said so I have to go back and look 🙂
PREVIOUS oops
Actually CrazyEights ... THAT WORKED!! Thank you ... I don't know why but I have had many problems with that QUOTED_IDENTIFIER
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply