July 13, 2014 at 5:17 am
FYI QUOTENAME function Return Types nvarchar(258), so be aware
July 13, 2014 at 5:35 am
Yep and the returned string includes single quotes so no need to include additional quotes before tbe plus sign
July 13, 2014 at 5:49 am
For fun and to further on my previous post on simplifying nested string handling, here is another method which uses a pass-through parameter for sp_executesql.
😎
DECLARE @ONELL NVARCHAR(50) = N'O''Nell';
DECLARE @PARAM NVARCHAR(100) = N'@ONELL NVARCHAR(50)';
PRINT @@NESTLEVEL;
PRINT @ONELL;
DECLARE @SQL_STR NVARCHAR(MAX) = N'
PRINT @@NESTLEVEL
PRINT @ONELL
DECLARE @PARAM NVARCHAR(100) = N''@ONELL NVARCHAR(50)'';
DECLARE @SQL_STR NVARCHAR(MAX) = N''
PRINT @@NESTLEVEL
PRINT @ONELL
DECLARE @PARAM NVARCHAR(100) = N''''@ONELL NVARCHAR(50)'''';
DECLARE @SQL_STR NVARCHAR(MAX) = N''''
PRINT @@NESTLEVEL
PRINT @ONELL
PRINT @@NESTLEVEL
PRINT @ONELL
''''
EXEC SP_EXECUTESQL @SQL_STR,@PARAM,@ONELL;
''
EXEC SP_EXECUTESQL @SQL_STR,@PARAM,@ONELL;
'
EXEC SP_EXECUTESQL @SQL_STR,@PARAM,@ONELL;
Results
0
O'Nell
2
O'Nell
4
O'Nell
6
O'Nell
6
O'Nell
July 14, 2014 at 7:36 am
Agreed. Rather than trying to keep track of the quotes, I use the tilde ~ to represent my single quote, then do a replace at the end.
July 14, 2014 at 7:48 am
annlcarey (7/14/2014)
Agreed. Rather than trying to keep track of the quotes, I use the tilde ~ to represent my single quote, then do a replace at the end.
Careful with that. The tilde is a reserved character - maybe it could cause you issues somehow, somewhere:
http://msdn.microsoft.com/en-us/library/ms173468(v=sql.110).aspx
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 14, 2014 at 12:13 pm
1. The outside 2 single quotes delimit the string.
2. On the inside of the string you must have 2 single quotes for each single quote you are representing.
I am most appreciative of this article if for no other reason than these two pointers. I rarely write dynamic sql from scratch (usually fixing it) and when I do I typically struggle with the single quotes. I was able to quickly complete the "homework" just by applying these two rules.
They are now part of my notes and I thank you for that.
July 14, 2014 at 12:18 pm
PS: I’m surprised Celko hasn’t jumped on this thread screeching for even broaching the subject of dynamic sql much less enabling it. I hope he’s okay.:-)
July 14, 2014 at 1:00 pm
jshahan (7/14/2014)
1. The outside 2 single quotes delimit the string.
2. On the inside of the string you must have 2 single quotes for each single quote you are representing.
I am most appreciative of this article if for no other reason than these two pointers. I rarely write dynamic sql from scratch (usually fixing it) and when I do I typically struggle with the single quotes. I was able to quickly complete the "homework" just by applying these two rules.
They are now part of my notes and I thank you for that.
Thanks! That makes the whole thing worth while. dynamic SQL can be a bear if you don't work with it much and no matter how many times people say "Dynamic SQL is bad" you still have to deal with it in legacy code.
You might also look some blog posts I wrote:
http://sqlstudies.com/2013/06/12/best-practice-recommendations-for-writing-dynamic-sql/
http://sqlstudies.com/2013/07/01/writing-dynamic-sql-a-how-to/
http://sqlstudies.com/2013/07/22/generic-dynamic-sql-stored-procedure/
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 15, 2014 at 8:29 am
This is a very good brain teaser as well as a good test for someone to show they know SQL very well.
Awesome article. Looking for your answer now. 🙂
Thanks Kenneth Fisher,
Britt
July 15, 2014 at 9:50 am
I would have thought that the option specify an escape character or a literal string would have been added years ago. I guess it's not that important in the bigger scheme of things.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
July 15, 2014 at 10:31 am
Let's not forget that this is also about the ability to read dynamic SQL. And it doesn't matter if you like or dislike dynamic SQL at some point you are going to have to be able to read it.
Here is a simple example of generating a series of commands to print out the name of a database.
declare @sql nvarchar(max)
SET @sql = N'SELECT ''PRINT ''''''+name+''''''''' +
N' FROM sys.databases'
EXEC sp_executesql @sql
Take a minute and count up the number of 's. Or better yet try to write it yourself. And remember that this is a SIMPLE example.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
December 19, 2014 at 8:31 pm
It's interesting to see how people differ in their preference on this. For me CHAR(39) has been my preference for a long time.
Nice article though.
December 19, 2014 at 10:43 pm
Dynamic SQL is a necessary, lazy throwback. And if Microsoft had any self respect they would kill it unceremoniously this instant in favor of ANYTHING resembling useful.
December 19, 2014 at 10:58 pm
Not I hope, by breaking all existing code in production - this they did when they killed basic to introduce dot nyet basic.
Viewing 14 posts - 61 through 73 (of 73 total)
You must be logged in to reply to this topic. Login to reply