Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Syntax Question Expand / Collapse
Author
Message
Posted Tuesday, November 26, 2013 10:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:20 PM
Points: 47, Visits: 126
To all:

I am working on a SQL query as follows and am hoping for help troubleshooting and answering questions:

DECLARE @Param nvarchar(max)
SET @Param = ''
SELECT @Param = @Param + '''' + ParameterValue + ''''+ N', '
FROM ScriptParams WHERE scripttype='TestParam'
select @Param = substring( @Param, 1, (LEN( @Param)-1))

DECLARE @sql nvarchar(max)

set @SQL = 'SELECT Company_NO, '' as CompanyName, CASE isNull(post_date, 0) WHEN 0 THEN DATEPART(yyyy, DATEADD(mm, - 6, create_date)) ELSE DATEPART(yyyy, DATEADD(mm, - 6, post_date)) END AS Year FROM CompanyOrder WHERE CompanyType IN (@param)'

EXEC (@sql)

In the statement above I am extracting three columns:

1. Company_No
2. CompanyName (Which is blank for now)
3. Year (The date field pulled will depend on if one is NULL, the decision is made by using a case statement).

Also, I am filtering by using a SQL In clause.

I believe to run my query I must use @SQL for the IN clause to work. However, when I do this, my case statement does not work (it prints the logic text).

As I am learning, can someone help me understand why this is happening? And how I can solve the problem?
Post #1517760
Posted Tuesday, November 26, 2013 11:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:26 PM
Points: 2,763, Visits: 5,914
You might want to change your @sql definition to something like the following:

set @SQL = 'SELECT Company_NO, 
'''' as CompanyName, --Added extra quote marks
CASE isNull(post_date, 0)
WHEN 0 THEN DATEPART(yyyy, DATEADD(mm, - 6, create_date))
ELSE DATEPART(yyyy, DATEADD(mm, - 6, post_date)) END AS Year
FROM CompanyOrder
WHERE CompanyType IN (' + @param + ')'

Notice that this is vulnerable to SQL injection. To prevent this you need to change the code to a parametrized code. I'll work on it and post again.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1517766
Posted Tuesday, November 26, 2013 11:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:26 PM
Points: 2,763, Visits: 5,914
Here's an example of a parametrized dynamic query using sp_executesql. Note that I'm using the DelimitedSplitN4K to split the values because you can't use IN for a single variable with delimited values.

DECLARE @Param nvarchar(4000)
SET @Param = '12,15,32'
--SELECT @Param = @Param + '''' + ParameterValue + ''''+ N', '
--FROM ScriptParams WHERE scripttype='TestParam'
--select @Param = substring( @Param, 1, (LEN( @Param)-1))

DECLARE @sql nvarchar(max)

set @SQL = 'SELECT Company_NO,
'''' as CompanyName,
CASE isNull(post_date, 0)
WHEN 0 THEN DATEPART(yyyy, DATEADD(mm, - 6, create_date))
ELSE DATEPART(yyyy, DATEADD(mm, - 6, post_date)) END AS Year
FROM CompanyOrder
CROSS APPLY dbo.DelimitedSplitN4K( @paramIN, '','')
WHERE Item = CompanyType'
EXECUTE sp_executesql @SQL, N'@paramIN nvarchar(4000)', @ParamIN = @Param;

Even better would be to eliminate the dynamic code:
SELECT Company_NO, 
'' as CompanyName,
CASE isNull(post_date, 0)
WHEN 0 THEN DATEPART(yyyy, DATEADD(mm, - 6, create_date))
ELSE DATEPART(yyyy, DATEADD(mm, - 6, post_date)) END AS Year
FROM CompanyOrder c
WHERE c.CompanyType IN ( SELECT ParameterValue FROM ScriptParams s WHERE scripttype='TestParam')




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1517774
Posted Tuesday, November 26, 2013 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:20 PM
Points: 47, Visits: 126
Thank you Luis, I appreciate your help! =)
Post #1517789
Posted Tuesday, December 03, 2013 12:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:20 PM
Points: 47, Visits: 126
Hi,

I would like to get the results of three different queries and put them all into one temp table. I am trying as follows but does not seem to be working. Is there a better way to do this?

INSERT INTO #Temp
EXECUTE sp_executesql @SQL
EXECUTE sp_executesql @SQL2
EXECUTE sp_executesql @SQL3
Post #1519363
Posted Tuesday, December 03, 2013 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
rayh 98086 (12/3/2013)
Hi,

I would like to get the results of three different queries and put them all into one temp table. I am trying as follows but does not seem to be working. Is there a better way to do this?

INSERT INTO #Temp
EXECUTE sp_executesql @SQL
EXECUTE sp_executesql @SQL2
EXECUTE sp_executesql @SQL3


You have 3 statements going on there. The first is an insert exec followed by two more execs.

In order for this to work you will need to specify insert for each statement.

INSERT INTO #Temp
EXECUTE sp_executesql @SQL

INSERT INTO #Temp
EXECUTE sp_executesql @SQL2

INSERT INTO #Temp
EXECUTE sp_executesql @SQL3

Please be careful about sql injection. Your original code was wide open and this doesn't have any parameters so I assume it must still be vulnerable. I urge you to do yourself and your company a favor and read about sql injection.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1519398
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse