Using sp_executesql Parameters –#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t used sp_executesql much. Instead, my habitually way of executing dynamic SQL has been with EXEC(). There are a few differences between these commands, but I had to look at sp_executesql recently and realized I didn’t know much about it.

One of the neat things with sp_executesql is that you can pass in parameters.  That’s pretty cool. I hadn’t ever bothered, but if you read the docs, you’ll see that if you execute the same code over and over, with different parameters, you might get the same execution plan. This can be a performance boost.

NOTE: THIS IS NOT ALWAYS BETTER. It can be.

I’m not going to delve into deep details, but Kimberly Tripp does so read her post (and then write your own thoughts).

The Code

Here’s some code to demonstrate. I have a simple table with 3 columns to insert. In this case, here’s my insert:

INSERT EventLogger VALUES (@m, @d, @u)

Now, I went to use this over and over, but with different values for the parameters. Obviously I can just do this:

SET @m = ‘Error Message’

INSERT EventLogger VALUES (@m, @d, @u)

SET @m = ‘New Error Message’

INSERT EventLogger VALUES (@m, @d, @u)

However, imagine that I’m building this INSERT string dynamically because it’s more complex. How do I execute this over and over with new values? With EXEC(), I rebuild the string. With sp_executesql, I do this:

DECLARE @cmd NVARCHAR(MAX)
DECLARE @dt DATETIME = GETDATE();
DECLARE @msg VARCHAR(200) = ‘An error occured’;
DECLARE @usr VARCHAR(10) = ‘Steve’;
DECLARE @p NVARCHAR(500);

SELECT @cmd = N’INSERT EventLogger VALUES (@m, @d, @u)’

SELECT @p = N’@m varchar(200), @d datetime, @u varchar(10)’

EXEC sp_executesql @cmd, @p, @m = @msg, @d = @dt, @u = @usr;

SELECT @dt = GETDATE()
     , @msg = ‘A new error occured’
     , @usr = ‘Bob’;

EXEC sp_executesql @cmd, @p, @m = @msg, @d = @dt, @u = @usr;
GO
SELECT top 10
  *
FROM dbo.EventLogger AS el

Now, I check the table:
2016-06-22 15_00_08-Settings

I thought that was cool.

SQLNewBlogger

This isn’t a deep post. It’s a light look, with a little explanation. I’ll do more later. However, I’m hoping this serves as a way to show you how to start investigating a topic. I’ve spent a bit of time experimenting and learning. I’m fairly confident I could play and use sp_executesql more.

You could do this as well, start digging into a topic and then show how you’re learning.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.