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.

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.

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://darkslategrey-bat-805937.hostingersite.com)