Solved

How to print from a stored procedure

Posted on 2012-04-13
7
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2012 AOG and SQL2014 AOG 76 61
How to handle time out inside the stored procedure 10 27
online  environment for testing sql queries 5 31
SQL Syntax 6 41
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

726 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