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;Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
4 Comments. Leave new
Pinal Sir is Excellent.
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
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?
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