November 26, 2013 at 10:31 am
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?
November 26, 2013 at 11:10 am
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.
November 26, 2013 at 11:23 am
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')
November 26, 2013 at 12:02 pm
Thank you Luis, I appreciate your help! =)
December 3, 2013 at 12:24 pm
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
December 3, 2013 at 1:36 pm
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 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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply