August 12, 2013 at 8:59 pm
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?
August 12, 2013 at 9:54 pm
garyh2k3 (8/12/2013)
When setting my @sql statement toFROM
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/
August 12, 2013 at 10:33 pm
August 13, 2013 at 12:37 am
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
August 13, 2013 at 8:18 am
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.
August 13, 2013 at 8:20 am
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/
August 13, 2013 at 9:01 am
Yes I did.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy