Solved

How to print from a stored procedure

Posted on 2012-04-13
7
145 Views
Last Modified: 2012-04-15
I am trying to debug a stored procedure. It would be helpful to be able to display the output at points.

I tried using PRINT sometext

but do not see it in either the output window or the Messages window. The messages window shows the messge "Command(s) completed successfully"

Is there some sort of setup I need to do to display messages from PRINT statements?

I know this is trivial, but I  have searched for a solution.

Thanks
0
Comment
Question by:KWDave
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 37844934
"print" will not print anything in case the variable is null. also don't forget to convert the value to varchar, char etc.

You can actually debug a stored procedure in SSMS. Just use the  button  on the right of "Execute" button ( the one with with the green "play" like icon)
0
 

Author Comment

by:KWDave
ID: 37845186
Thanks for your response.

I was trying to output text as a test - I used PRINT N'Test Print' as the print string.

I also tried using RaiseError, with the same (non) result.

I thought the print function was supposed to print to the console in either the Messages section, or the Output.

The debug function puts it's results in the Output window.

I was wondering if the problem was one of setup or an option to direct the PRINT output.

I figure it is something stupid I didn't do on setup, but can't find out what.



I may be able to function with the DEBUG, but would like to have the functionality of the PRINT
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37846447
I thought the print function was supposed to print to the console in either the Messages
section, or the Output.

You can easily verify that in SSMS PRINT outputs the result to the Messages window.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 18

Accepted Solution

by:
vasto earned 500 total points
ID: 37846492
I am not sure if you are talking about SSMS or Visual Studio.
Message window in SSMS may show data printed by you, but also status messages, so you may need to scroll up to find your data.
0
 

Author Closing Comment

by:KWDave
ID: 37846702
When i isolated the Print N'Test' it worked.

I will have to figure out how to get it to function within the code.

It is a large stored procedure and it would be great to be able to see intermediate states. I will work with the debug function as suggested to see if that does what i need.

Thanks,
Dave
0
 
LVL 18

Expert Comment

by:vasto
ID: 37846793
Dave, there are 2 possible reasons to not see anything printed :
1. your code is not going trough the line because of a "if" logic
2. you are trying to print a variable which is null or empty string.  Let's say your variable name is  @SampleText; try this:

print '@SampleText value = ' + isnull(@SampleText,'<<string is empty>>')

if @SampleText has value 'some text' you will see
@SampleText value = some text

if @SampleText is empty but not null you will see
@SampleText value =

if @SampleText is null you will see
@SampleText value = <<string is empty>>
0
 

Author Comment

by:KWDave
ID: 37848713
Thanks for the input.

I suspect the problem was due to some if logic.

Dave
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question