stored procedure with return value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored procedure with return value

Author

Topic

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-10-31 : 00:47:49

i want to return a values to application program in.net when a stored
procedure is called.how to declare a return variable with varchar datatype. i am have to declare it as output parameter
please modify this if u can

create proc test
@id, @dept,@sal
as
declare @message
update tab1 set id=4 where id=@id
if(@@rowcount=0)
begin
set @message='error in updating'
return @message
end
if(@@rowcount=0)
begin
set @message='error in inserting'
return @message
end
or i have to declare @message as output parameter.
To return string am i have to set return type of stored procedure? if so where i have to set
/////////////////////////////////////////////////////

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-31 : 00:53:31

you have to use the OUTPUT parameter


create proc test
@id int, @dept int, @sal int, @message varchar(100) OUTPUT
as
declare @message
update tab1 set id=4 where id=@id
if(@@rowcount=0)
begin
    set @message='error in updating'
    return @message
end
if(@@rowcount=0)
begin
    set @message='error in inserting'
    return @message
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 00:54:30

create proc test
@id int, @dept varchar(50), @sal int, @message varchar(1000) OUT
as
~~declare @message~~
update tab1 set id=4 where id=@id
if(@@rowcount=0)
begin
set @message='error in updating'
return ~~@message~~
end
insert tab1 values(id)
if(@@rowcount=0)
begin
set @message='error in inserting'
return ~~@message~~
end

call with test x, x, x, @message out

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 00:55:44

Peter Larsson
Helsingborg, Sweden

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-10-31 : 01:15:49

THANK U
Mr.K H Tan
Mr.Peter Larsson

Salmon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources