SQL SERVER – How to See Scripts Executing in sp_executesql?

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)

, ,
Previous Post
SQL SERVER – How to Pass Parameters to the Stored Procedure?
Next Post
SQL SERVER – Interesting Observation – Count of Temporary Table Re-generations

Related Posts

4 Comments. Leave new

  • Bubun Dash (@BubunyellowDash)
    July 18, 2017 9:13 pm

    Pinal Sir is Excellent.

    Reply
  • 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
  • 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

Leave a Reply

Menu