Menu
I can use the PRINT statement in a stored procedure to debug my code. I see the output in the Messages tab of SQL Server Management Studio. How can I dump one or more entire SELECT statement outputs to that Messages tab?
My stored procedure returns several output variables so returning a single dataset isn't an option here. I am struggling with finding a good way to debug my complex procedures.
342
Declare @SumVal int; Select @SumVal=Sum(Amount) From Expense; Print @SumVal; You can, of course, print any number of fields from the table in this way. Of course, if you want to print all of the results from a query that returns multiple rows, you'd just direct your output appropriately (e.g. to Text).
We can not directly use stored procedures in a SELECT statement.
This provides a way to save a result returned from one query, then refer to it later in other queries. The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving.
Set the "Results to Text" option and your Results and Messages tabs will be consolidated to a single tab, combining your PRINT and SELECT statements.
To set Results to Text, either:
170
Getting the entire contents of a query to print to the messages window would probably be more trouble than it's worth. Instead, I would recommend debugging the procedure from a query window. What you can do is add an optional parameter to your procedure, with a default of NULL. Your app won't be passing it, so you can use this to your advantage. Ex:
Alter Procedure Foo(@Col1 int, @col2 varchar(20), @Debug Bit = NULL)
As
SET NOCOUNT ON
If @Debug = 1
Begin
Select * From SomeTable Where Col1 = @Col1
End
-- The rest of your code here
Then, when you call this procedure from a query window, simply pass in a value of 1 to the procedure for that @Debug parameter
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
