SQL SERVER – How to See Scripts Executing in sp_executesql?

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.

1 2 3 4 5

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