473,834 Members | 1,854 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

return val of stored procedure in query analyzer

hi ng,

i am newbie to sqlserver and my problem seems simple, but i didn't find
information about it:
How can i display the
RETURN @x
value of a stored procedure in the sql analyzer of the sqlserver?
thanks a lot,
d
Jul 20 '05 #1
2 3415
Daniel wrote:
hi ng,

i am newbie to sqlserver and my problem seems simple, but i didn't find
information about it:
How can i display the
RETURN @x
value of a stored procedure in the sql analyzer of the sqlserver?
thanks a lot,
d


i just realized that the return value of my stored procedure with
inserts is the number of datasets inserted, even if i write

DECLARE @i int
set @i=9
RETURN @i

so, the question is: is it possible to return i to the invoking application?
thanks
d
Jul 20 '05 #2
Hi Daniel,

One approach that I use is to create a temp table in the stored procedure.
For example: Create a table with fields ID as IDENTITY and Message as a
varchar(1000). Temp tables have a prefix of # for example, #Message.

Then as the Stored Procedure executes, you can INSERT into Message whatever
messages you want. You can return row counts, all kinds of stuff re the
#Message table. This is extreamly helpful for long stored procedures.

At the end of the Stored Proc, do the following:

Select Message from #Message.

This will return all the rows to the calling program if you have opened up
the call to the stored proc as returning a recordset.

Then at the end of the stored procedure, just drop the temp table.

Drop Table #Message.

Hope this helps.

Barry




"Daniel" <da**********@z dv.uni-tuebingen.de> wrote in message
news:cl******** **@newsserv.zdv .uni-tuebingen.de...
Daniel wrote:
hi ng,

i am newbie to sqlserver and my problem seems simple, but i didn't find
information about it:
How can i display the
RETURN @x
value of a stored procedure in the sql analyzer of the sqlserver?
thanks a lot,
d


i just realized that the return value of my stored procedure with inserts
is the number of datasets inserted, even if i write

DECLARE @i int
set @i=9
RETURN @i

so, the question is: is it possible to return i to the invoking
application?
thanks
d

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
2319
by: Dragonhunter | last post by:
Hello, The aspfaq.com seems to really push stored procedures, and I hear the same advice here all the time. So I want to take the advice. Is it possible to create and practically maintain, delete, use, etc.. stored procedures soley from asp (i.e., no GUI or console- like being hosted on Brinkster)? The tutorial on aspfaq.com mentions that stored procedures can be
3
22152
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my application server can talk to the database. I've determined the failure occurs when the the following statement is executed: cstmt.execute(); (due to the failure of println statements placed afterwards). I get the following error after trying to...
5
7297
by: Warren Wright | last post by:
Hi group, I have a select statement that if run against a 1 million record database directly in query analyzer takes less than 1 second. However, if I execute the select statement in a stored procedure instead, calling the stored proc from query analyzer, then it takes 12-17 seconds. Here is what I execute in Query Analyzer when bypassing the stored procedure:
4
9564
by: shyner | last post by:
Hi Everyone, I've been battling this for two days with no luck. I'm using SQL Server 2000. Here's the mystery: I've got a stored procedure that takes a single varchar parameter to determine how the result set is sorted. Here it is: CREATE PROCEDURE spDemo @SortField varchar(30)
3
2543
by: Gary Besta | last post by:
I am trying to add a simple case statement to a stored procedure or user defined function. However when I try and save the function/procedure I get 2 syntax errors. Running the query in query analyser works fine and a result is given with no syntax errors. I believe its something to do with the spaces in the field names. Not my choice as its an existing system I have to work around. Any help greatly appreciated SQL Query
1
2083
by: dbuchanan | last post by:
VB.NET 2003 / SQLS2K The Stored Procedure returns records within Query Analyzer. But when the Stored Procedure is called by ADO.NET ~ it produced the following error message. --------------------------- Exception Message: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. ---------------------------
2
2752
by: Crazy Cat | last post by:
Hi, I wrote the following in the SQL Server 2005 Express Management Studio Query Analyzer and hit execute. Even though Query Analyzer indicated success when I hit refresh on the stored procedure folder the procedure does not show up in the stored procedure list. When I entered 'exec Test' I get an error indicating that SQL Server 2005 can't find the procedure. I executed the following code 'select * from sys.all_objects where name like...
9
7672
by: vikram.mankar | last post by:
I have a stored procedure thats transferring/processing data from one table to two different tables. The destination tables have a unique value constraint as the source tables at times has duplicate records and this will prevent the duplicates from being reported. When the stored procedure (which includes a cursor) is executed through query analyzer, it runs fine, and reports an error everytime it sees a duplicate value (as expected). It...
1
2305
by: central_scrutinizer | last post by:
I have an ASP that has been working fine for several months, but it suddenly broke. I wonder if windows update has installed some security patch that is causing it. The problem is that I am calling a stored procedure via an ASP (classic, not .NET) , but nothing happens. The procedure doesn't work, and I don't get any error messages. I've tried dropping and re-creating the user and permissions, to no avail. If it was a permissions...
0
9800
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10516
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9339
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6960
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5629
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5800
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4429
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3987
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3085
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.