adding single quotes around a value output by a function

Question

Post reply

adding single quotes around a value output by a function

winston Smith

SSCoach

Points: 19500
More actions
May 11, 2010 at 9:43 am

#224406

Im writing a script that will output the text to execute a sproc a bunch of times. the text is output as varchar. I can do this fine with actual values, as its easy to get the quotes around them, but there is a date value required, and i cannot get it to output with single quotes:
select 'EXEC Sproc '
+ '''value_1''' + ','
+ convert(varchar(50),getdate(),121)
Required Output:
EXEC Sproc 'value 1','2010-05-11 08:39:44.003'
Actual Output:
EXEC Sproc 'value 1',2010-05-11 08:39:44.003
note there are no quotes around the date value. This date value could be anything as it will be pulled from a table. im just using getdate() to simplify the details of the query.
I cant put 3 single quotes around the function as it has this effect:
select 'EXEC Sproc '
+ '''value_1''' + ','
+ ''' + convert(varchar(50),getdate(),121) + '''
Result:
EXEC Sproc 'value_1',' + convert(varchar(50),getdate(),121) + '
Thanks for any help you can give.

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

add a forth single quote on each side and that will result in a quote around the date.

select 'EXEC Sproc '

+ '''value_1''' + ','

+ '''' + convert(varchar(50),getdate(),121) + ''''

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

winston Smith SSCoach Points: 19500 More actions · Answer 2

awesome! thanks. I didnt try that as i added two quotes, then tried 3, and thought then that if i kept adding quotes it would make no difference.

Thanks!

Dohsan SSCrazy Points: 2972 More actions · Answer 3

I believe you can also use the QUOTENAME function here

http://msdn.microsoft.com/en-us/library/ms176114.aspx

SELECT'EXEC Sproc '

+ '''value_1''' + ','

+ QUOTENAME(CONVERT(VARCHAR(50),GETDATE(),121),'''')

Output:

EXEC Sproc 'value_1','2010-05-11 17:30:44.937'

WayneS SSC Guru Points: 95460 More actions · Answer 4

Dohsan (5/11/2010)
I believe you can also use the QUOTENAME function here
http://msdn.microsoft.com/en-us/library/ms176114.aspx
SELECT'EXEC Sproc '
+ '''value_1''' + ','
+ QUOTENAME(CONVERT(VARCHAR(50),GETDATE(),121),'''')
Output:
EXEC Sproc 'value_1','2010-05-11 17:30:44.937'

Once you start dealing with this many quote marks, I find it easier to utilize char(39). Thus, it would be

SELECT'EXEC Sproc '

+ QUOTENAME('value_1', char(39)) + ','

+ QUOTENAME(CONVERT(VARCHAR(50),GETDATE(),121),char(39))

Or from Dan's example:

select 'EXEC Sproc '

+ char(39) + 'value_1' + char(39) + ','

+ char(39) + convert(varchar(50),getdate(),121) + char(39)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2