Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Return value on stored procedure

Status
Not open for further replies.

Guest_imported

New member
Jan 1, 1970
0
I have a simple stored procedure as shown below.

alter Procedure GetUserInfoDate(@UserName varchar(30),@Udate datetime output)
as
select @UDate = UDate from calendar
where username=@Username

I have 2 parameters, Username which is an input and Udate which is an output parameter.


But when I execute the procedure like
exec GetUserInfoDate "Rebekah"," "
I dont get any output.It just says that the command executed successfully but no result or output is shown.

Any help on this Please!!!
Thx in advance
Rebekah
 

You need to define the Output parameter in the calling statement.

Declare @ud datetime
exec GetUserInfoDate "Rebekah", @udate=@ud Output
Print @ud
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
This prints only the last record of that table.Actually I am calling this stored procedure from ASP.
 

You can only return one value to a variable. How many records does Rebekah have on the table? Do you want to return a record set rather than single value? I guess I don't undersand your intent. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
I need to return a record set as you said.

I have another doubt as well.

This is my procedure which has to return 6 rows but it returns 192 rows and finally gives an error as
Server: Msg 217, Level 16, State 1, Procedure calendar1, Line 14
Maximum stored procedure nesting level exceeded (limit 32).

This error is got when I execute the procedure as :
exec calendar1 "Rebekah"


Procedure:

alter procedure calendar1
(
@FName varchar(20)
)
as
Begin

Select *
from calendar
where Username =@FName or Username = "All"
End

Can you kindly find out the mistake I committed?

thx
Rebekah

 
Kindly ignore the previous posting. It was my mistake. Actually I included the exec calendar "REbekah" within the procedure itself and so it looped infinitely. Sorry to disturb You.
But I want the output as a recordset and so what has to be done.Do I need to loop it?

thx
 
The procedure as written should return a record set. However, you should add SET NOCOUNT ON at the beginning of the procedure and SET NOCOUNT OFF at the end to prevent informational messages about number of rows returned. These messages can cause problems in other applications.
[tt]
Alter Procedure calendar1
(@FName varchar(20))
As

SET NOCOUNT ON

Begin
Select *
From calendar
Where Username =@FName
Or Username = "All"
End

SET NOCOUNT OFF[/tt] Terry L. Broadbent
Programming and Computing Resources
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top