Query Help - Embed a \ string in a string

Question

Query Help - Embed a \ string in a string

Sean Grebey

Ten Centuries

Points: 1159
More actions
August 6, 2013 at 1:50 pm

#276729

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]

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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, '

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Stefan_G SSCertifiable Points: 6609 More actions · Answer 2

This should work:

[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]

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 3

Sean Grebey

Ten Centuries

Points: 1159

August 6, 2013 at 2:23 pm

#1639121

Thanks guys!

Sean Lange SSC Guru Points: 286589 More actions · Answer 4

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/