sp returning more than one values

Question

sp returning more than one values

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
March 4, 2004 at 4:05 am

#109071

Plz suggest the syntax of returning more than three output parameters in Stored procedure
Situation is
if insert successfull it returns false
other wise returns 4 values........
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Can you post your code along with the *error* you get and what the result should look like?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

*Prashant Thakwani SSCommitted Points: 1827 More actions · Answer 2

you cann't get the four return values from a SP. U have to use the select statement for this.

Prashant Thakwanithakwani_prashant@yahoo.co.in

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Are you sure on this?

Extending the examples from BOL

IF OBJECT_ID('get_sales_for_title') IS NOT NULL

DROP PROCEDURE get_sales_for_title

GO

CREATE PROCEDURE get_sales_for_title

@title varchar(80), -- This is the input parameter.

@ytd_sales int OUTPUT, -- This is the output parameter.

@advance int OUTPUT

AS

-- Get the sales for the specified title and

-- assign it to the output parameter.

SELECT @ytd_sales = ytd_sales, @advance = advance

FROM titles

WHERE title = @title

RETURN

GO

DECLARE @ytd_sales_for_title int

DECLARE @advance_for_title int

-- Execute the procedure with a title_id value

-- and save the output value in a variable.

EXECUTE get_sales_for_title

"You Can Combat Computer Stress!", @ytd_sales = @ytd_sales_for_title OUTPUT,

@advance = @advance_for_title OUTPUT

-- Display the value returned by the procedure.

PRINT 'You Can Combat Computer Stress!": ' + convert(varchar(6),@ytd_sales_for_title)

+ ' ' + convert(varchar(6),@advance_for_title)

GO

yields

You Can Combat Computer Stress!": 18722 10125

I don't know how many OUTPUT parameters you can define, but obviously it's more than one. Assumption is of course your query returns only one row.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 4

Frank should i do in this way(select statement)?

CREATE PROC CM_SP_InsCommonMaster

@CMD_V_CODE varchar(15),

@CMD_V_DESC varchar(50),

@CMD_C_INACTIVE char(1),

@CMD_D_INACDATE datetime,

@CMD_V_WONO varchar(6)--optional

as

declare @err int

insert into ZCM_CMD_COMMONMASTER(CMD_V_CODE ,

CMD_V_DESC,

CMD_C_INACTIVE,

CMD_D_INACDATE,

CMD_V_WONO )

values(@CMD_V_CODE ,

@CMD_V_DESC ,

@CMD_C_INACTIVE ,

@CMD_D_INACDATE,

@CMD_V_WONO )

set @err =@@error

if @err<>0

return 1

else

select CMD_V_CREATEDBY,

CMD_D_CREATEDDATE,

CMD_V_MODIFIEDBY,

CMD_D_MODIFIEDDATE

from

ZCM_CMD_COMMONMASTER

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Am I reading it right that you want to return an error code in case such occurs, otherwise the last inserted row?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 6

Situation is

if insert successfull it returns false

other wise returns 4 values

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Look at the explanation of RETURN in BOL.

The RETURN statement unconditionally terminates a query, stored procedure, or batch. None of the statements in a stored procedure or batch following the RETURN statement are executed.

Does this help?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]