CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
Jetnor
Jetnor

Reputation: 531

Change dynamic SQL from using Exec to sp_executesql

I am a beginner in SQL and esspecially dynamic sql execution.

I am trying to convert some dynamic sql which is inside a store procedure from using EXEC to using sp_executesql.

The issue I have is I don't know what are the exact rules for representing multiple lines of dynamic sql. The existing code is in the form of:

 Set @cmd = 'WITH vdate AS' + char(13)
           +' (SELECT valueID,' + char(13)
           +'username)' +char(13)
 +'FROM dbo.table1' + char(13)
 +'WHERE username = ''' + convert(varchar(11), @username, 106) + ''' AND' + char(13)
 Exec (@cmd)

The above is just a snippet of what it looks like. There are alot more lines and more complicated stuff going on.

I now want to be able to use the sp_executesql way of executing this code because I want to change the username attribute to be a table which accepts multiple usernames. Please can you advise me what the syntax is for this and how do i make the line before last work?

I have seen that code looks very similar so what i did I changed the +' to be N' at the start of each line and the code compiled but it didn't execute when I tried to use the query in my application.

Thanks, Jetnor.

Upvotes: 0

Views: 1452

Answers (1)

Steve Pettifer
Steve Pettifer

Reputation: 2043

The only difference is that instead of EXEC (@cmd) you would use EXEC sp_executesql @cmd. Technically speaking, sp_executesql takes NVARCHAR arguments. In order to create an NVARCHAR string you should prefix the string with N like so:

DECLARE @cmd NVARCHAR(1000);
SET @cmd = N'My string'

This denotes that the string is a Unicode string which may contain national character sets. Of course you can omit the N but if you have anything other than ASCII characters in it then it won't work properly and you'll end up with strings that contain a lot of question marks instead of your special characters (CJK - Chinese, Japanese, Korean - character sets for instance) so it is good practice to always prefix NVARCHAR strings with the N.

Some of your confusion arises because you are replacing the string concatenation operator, +, with the national character set identifier, N. You will need both. It is always worth including proper spacing in your code, especially in dynamic SQL because it's easier to read. My preference is to add the spaces at the end of lines where possible, but that's just my personal preference. Also, drop the CHAR(13) stuff unless you really want to print it out in a certain way. Your example would become:

DECLARE @cmd NVARCHAR(1000)    --Note this is NVARCHAR, not VARCHAR.
SET @cmd = N'WITH vdate AS '
           + N'(SELECT valueID, '
           + N'username) '
 + N'FROM dbo.table1 ' 
 + N'WHERE username = ''' + convert(NVARCHAR(11), @username, 106) + ''' AND ' --Note that you are missing a chunk here - AND what?
 EXEC sp_executesql @cmd

Note that your CONVERT is incorrect: Firstly you should convert to NVARCHAR (if @username isn't already), and secondly the 106 part does nothing - that's for date formatting. Also you are missing the end of the statement as there is an AND and then nothing. It's always good to use PRINT @cmd to see if the SQL you've generated is valid (it will output the contents of @cmd and you can then copy that and paste it into a workshet in SSMS).

Now, you might want to have output parameters in your dynamic SQL as well which is no problem.

DECLARE @ID INT;
DECLARE @outputValue NVARCHAR(50);
DECLARE @cmd NVARCHAR(1000);
SET @cmd = N'SELECT @value = Value FROM MyTable WHERE ID = ' + 
               CAST(@ID AS NVARCHAR(5));
EXEC sp_executesql @cmd, '@value NVARCHAR(50) OUT', @outputValue OUT;
SELECT @outputValue;

Traditionally, the main reason for wanting to use sp_executesql as opposed to just EXEC is that you have a better chance of the execution plan being cached and therefore re-used later. I confess I'm not sure if that's still true or not, but sp_executesql is generally the preferred method of executing dynamic SQL. A lot of poeple have an instinctive hatred of dynamic SQL but frankly, like all things, it has it's uses so long as it isn't abused and used where it just isn't required.

Upvotes: 1

Related Questions