August 6, 2013 at 1:50 pm
I'm trying to modify an existing stored proc. This seems like of like a weird way to do it, but based on input parameters they are basically constructing a script string in the stored proc and then running it.
if @Interval = 'Monthly'
Begin
set @groupclause = ' Group by MONTH(ContractDate), YEAR(ContractDate) '
set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' + ' From Reporting.dbo.PrgSum '
set @orderclause = ' ORDER BY YEAR(ContractDate), MONTH(ContractDate)'
End
...
declare @bigquery nvarchar(max)=@sql+@whereclause+@groupclause+@orderclause
exec sp_executesql @bigquery
I'm trying to figure out how to insert a "/" between the Month and Year values:
[Code]set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' +
' From Reporting.dbo.PrgSum 'r[/CODE]
August 6, 2013 at 2:05 pm
First, I'm giving you the solution as you pictured it, but also another one that might be more effective. It's up to you to choose.
set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + ''/'' + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, '
set @sql = 'select SUBSTRING( CONVERT( char(10), GETDATE(), 103), 4, 7) as Date_Marker, '
August 6, 2013 at 2:23 pm
Thanks guys!
August 6, 2013 at 2:46 pm
Sean Grebey (8/6/2013)
I'm trying to modify an existing stored proc. This seems like of like a weird way to do it, but based on input parameters they are basically constructing a script string in the stored proc and then running it.
if @Interval = 'Monthly'
Begin
set @groupclause = ' Group by MONTH(ContractDate), YEAR(ContractDate) '
set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' + ' From Reporting.dbo.PrgSum '
set @orderclause = ' ORDER BY YEAR(ContractDate), MONTH(ContractDate)'
End
...
declare @bigquery nvarchar(max)=@sql+@whereclause+@groupclause+@orderclause
exec sp_executesql @bigquery
I'm trying to figure out how to insert a "/" between the Month and Year values:
[Code]set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' +
' From Reporting.dbo.PrgSum 'r[/CODE]
You also should change up this dynamic sql so that it is parameterized. The proc as you posted is vulnerable to 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 5 posts - 1 through 4 (of 4 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