Debugging queries inside stored procedures

Question

Consider this stored procedure. The actual content of the procedure does not matter, I use it only as an example:

CREATE PROCEDURE [dbo].[temp] @value1 varchar(50), @value2 varchar(50), @value3 varchar(50) as begin Select * From valuesTable (nolock) inner join valuesTable2 RL (nolock) on (ValuesTable.ID = RL.RuleId and RL.Type = 'Something') where @value1 = ValuesTable.RuleVal02 and cast(@value2 as float) > cast(ValuesTable.RuleVal03 as float) and cast(@value2 as float) < cast(ValuesTable.RuleVal04 as float) --and (@value3 = ValuesTable.RuleVal05) and (@value3 = ValuesTable.RuleVal05 or ValuesTable.RuleVal05 = -1) end 

Now imagine that this (not very complicated function) does not work. I already know how to debug it both from Visual Studio and from SQL Server Management Studio, however, both cases are absent:

The main point of failure of this function is the large request that it performs. What I would like to do is take this request, copy it to a new request window and start its execution and debug it, changing its various parts.

The main way to debug this request is to copy it into a new request window, get the parameters from the executable code, and then manually replace all @variables with their actual value. This works, but for me it seems like a lot of unnecessary work, and ideally I would like to receive a request because it is running on the server. With literal values ​​instead of parameters, for example:

 where 'actualValue' = ValuesTable.RuleVal02 and cast(4.2 as float) > cast(ValuesTable.RuleVal03 as float) and cast(4.2 as float) < cast(ValuesTable.RuleVal04 as float)... 

Since it sounded like something that I can only achieve from the profiler, I launched it. Then I configured events to capture the SP:StmtStarting to see instructions executed from stored procedures. To my surprise, however, the statements that I see in the profiler are still displayed with their parameter, and not with the actual value of the literal value.

Can I easily copy the actual query that is executed in the stored procedure, with the replaced parameters into the literal value of the parameter? Does the profiler have a flag? Or is there another way?

Answer 1

you can write your query in a variable using placeholders instead of actual values, replace placeholders at runtime and execute it with sp_executesql

obviously this is just a thing you should do for debugging, not production;)

here is an example: http://sqlfiddle.com/#!3/c8c43/5

there you have a query filled with actual content inside a variable

Answer 2

Try this simple workflow (it takes 5 seconds):

• Launch SSMS.
• Locate the stored procedure in the object browser.
• Right-click on it to display the context menu and select "Execute Stored Procedure ...".
• Set the values ​​of all parameters in the displayed form.
• SSMS creates a script for you in a new request tab (see the script at the end of this answer).
• Save this script for future reference.
• Run Debug -> Start debugging this script and then execute the EXEC command.
• Change the parameter values ​​and perform the following debugging.

 DECLARE @return_value int EXEC @return_value = [dbo].[temp] @value1 = N'1', @value2 = N'2', @value3 = N'3' SELECT 'Return Value' = @return_value GO