Solved

How to print from a stored procedure

Posted on 2012-04-13
7
142 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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