SQL SERVER – How to See Scripts Executing in sp_executesql?

July 18, 2017

Question: How to See Scripts Executing in sp_executesql?

Answer: Just the other day I had received this question in an email and I was not sure what the user actually asked.

I had to ask user to repeat his question or explain me with example. He sent me following screenshot with his question again.

I am recreating his screenshot for easy understanding here.

SQL SERVER - How to See Scripts Executing in sp_executesql? printmessage1

Along with above screenshot he also added that he does not want to use the SELECT command to see the string’s content as it also returns as a another resultset.

I really find the question very interesting and here is the answer to the question:

We can use the PRINT command to display the content of the string which is passed a parameter to sp_executesql. Here is the screenshot with the answer.

SQL SERVER - How to See Scripts Executing in sp_executesql? printmessage

You can see when you add a PRINT command it displays the content of the string in the message window and not in the resultset window.

I personally use this command very frequently when I am debugging any string operations in SQL Server.

Here is the script which user can run it against the AdventureWorks Database to recreate this demonstration.

DECLARE @LongString NVARCHAR(100);
SET @LongString = 'SELECT *';
SET @LongString = @LongString + ' FROM ';
SET @LongString = @LongString + ' [AdventureWorks2014].[HumanResources].[Department]';
EXEC sp_executesql @LongString;

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Stored Procedure

SQL SERVER – How to Pass Parameters to the Stored Procedure?

SQL SERVER – Interesting Observation – Count of Temporary Table Re-generations

SQL SERVER – Fix : Management Studio Error : Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

May 18, 2009

SQL SERVER – 2005 Security Best Practices – Operational and Administrative Tasks

March 21, 2007

SQL SERVER – A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function

May 3, 2012

4 Comments. Leave new

Bubun Dash (@BubunyellowDash)
July 18, 2017 9:13 pm
Pinal Sir is Excellent.
Reply
Bill Pennock
March 19, 2019 7:38 pm
Hmm, but what if you are using sp_executeSQL to parameterize and want to see what the executable string will look like?
N’SELECT name,summary,Wheel,Saddle,Pedal, RiderExperience
FROM Production.vProductModelCatalogDescription
WHERE (name+summary LIKE ”%”+@search+”%”)’,N’@search Varchar(20)’,@search=@QueryString)
trying to set a variable with that gives an error message at the comma before the parameter is declared
Reply
Robert T. Letts
June 30, 2019 11:20 pm
Awesome Dave. It works for me, but only if the PRINT statement is listed **before** the EXECUTE sp_executesql. Otherwise, even if they are separated by different BEGIN … END blocks, it seems that within my test harness the PRINT seems to not get executed. Why?
Reply
Mateen
February 6, 2020 10:56 am
How to see script of sp_executesql. example I have a stored procedure with name spStockCheck and I can see its script by right click on sp and modify OR simply execute sp_helpText spStockCheck.
sp_executesql is a system stored procedure how could i see its code/script because I have check it in system procedures but not found
Reply