Incorrect syntax near when using parameter in SET @SQL statement

Question

Incorrect syntax near when using parameter in SET @SQL statement

garyh2k3

SSC-Addicted

Points: 456
More actions
August 12, 2013 at 8:59 pm

#278591

When setting my @sql statement to
FROM
SATXFPMetrics
WHERE [Date] >= CONVERT(datetime,'''+ @StartDate +''',121)
group by [Date]'
--select @sql
--SELECT @SQL1
--exec sp_executesql
EXEC(N'sp_executesql N''' + @sql + @SQL1 + '''')
for example and calling the stored procedure exec ssrsSATXDailyFPTickets '6/1/2013' I get the incorrect syntax error near 6. Something is wrong with me adding my parameter. I can't figure out for the life of me what it is. I am assuming it has something to do with the apostrophes around my parameter or my syntax. I am sure it is something simple. Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

garyh2k3 (8/12/2013)
When setting my @sql statement to
FROM
SATXFPMetrics
WHERE [Date] >= CONVERT(datetime,'''+ @StartDate +''',121)
group by [Date]'
--select @sql
--SELECT @SQL1
--exec sp_executesql
EXEC(N'sp_executesql N''' + @sql + @SQL1 + '''')
for example and calling the stored procedure exec ssrsSATXDailyFPTickets '6/1/2013' I get the incorrect syntax error near 6. Something is wrong with me adding my parameter. I can't figure out for the life of me what it is. I am assuming it has something to do with the apostrophes around my parameter or my syntax. I am sure it is something simple. Any ideas?

Awfully sparse on details here.

You have a couple issues here. You need to parameterize your dynamic sql instead of building a string and executing it. That is vulnerable to sql injection. Also, there is no need to format a datetime variable unless your datatype is not datetime.

The syntax would be something like this:

declare @sql nvarchar(max)

set @sql = 'select *

from SATXFPMetrics

WHERE [Date] >= @StartDate

group by [Date]'

EXEC sp_executesql @sql, N'@StartDate datetime', @StartDate = @StartDate

The real question is why do you need dynamic sql for this? From what you posted I don't see any reason for it.

_______________________________________________________________

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/

garyh2k3 SSC-Addicted Points: 456 More actions · Answer 2

I have over 100 columns that I am summing up. I have exceeded the 4000 characters for my first @sql which is why I have a @sql and @SQL1. Based on another parameter will determine how the query is grouped. Either by day, week, or month. That was my intent in using the dynamic sql.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

If you read the BooksOnline entry for sp_executesql, you will notice that it can take an nvarchar(max) variable for both the dynamic sql query and for the definition of variables.

http://msdn.microsoft.com/en-US/library/ms188001(v=sql.100).aspx

garyh2k3 SSC-Addicted Points: 456 More actions · Answer 4

Thank you both for your responses. I was able to educate myself a little more about the sp_executesql and it is now behaving as I need it to.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

garyh2k3 (8/13/2013)
Thank you both for your responses. I was able to educate myself a little more about the sp_executesql and it is now behaving as I need it to.

Happy to help. I hope you used parameters instead of just building up the string.

_______________________________________________________________

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/

garyh2k3 SSC-Addicted Points: 456 More actions · Answer 6

garyh2k3

SSC-Addicted

Points: 456

August 13, 2013 at 9:01 am

#1640980

Yes I did.