get multiple return value in stored procedure in sql server

Question

I have a stored procedure like this:

ALTER procedure [dbo].[MobileDevice]
@platesourcecnt integer,
@plateCategory integer
as
begin
declare @SplateSourcecount integer,
@SplateCategory integer,
@Splatecode integer


select @SplateSourcecount= count(ps.PSID)from PlateSource_tbl ps 
if @SplateSourcecount <> @platesourcecnt
begin
select PSID,PS from  PlateSource_tbl where Deleted=0
end
else
 begin
return 1 

end

Select @SplateCategory=COUNT(pcat.PCID) from PlateCategory_tbl pcat
if @SplateCategory <> @plateCategory
 begin
select PCID,PC,PSID from  PlateCategory_tbl where Deleted=0
end
else
begin 
return 2
end

end

Here my platesource_tbl count is 13, if I pass value to @platesourcecnt =13 then I am getting return value 1 but that time my second select query is not working.
If I pass other parameter than 13 to @platesourcecnt then getting both working.
What is wrong with my stored procedure?
Can I get multiple return value in one stored procedure?

i can optimize this stored procedure better than this methode? — user3252014
– user3252014, Commented Jun 24, 2014 at 10:34

Azar · Accepted Answer · 2014-06-24 10:31:21Z

1

No you cannot return two times , one return will take you out,

Instead use Select 1 for return 1 , Select 2 for return 2

Or Declare two variables @return1, @return2 At the end of Proc Select @return1 'return1',@return2 'return2'

edited Jun 24, 2014 at 10:31

answered Jun 24, 2014 at 10:25

Azar

1,86715 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Azar · Accepted Answer · 2014-06-24 10:46:24Z

0

Try thiss Proc

ALTER procedure [dbo].[MobileDevice]
@platesourcecnt integer,
@plateCategory integer
as

if (select count(ps.PSID)from PlateSource_tbl ps )<> @platesourcecnt
select PSID,PS from  PlateSource_tbl where Deleted=0
else
raiserror('1st return',16,1)  

if (Select COUNT(pcat.PCID) from PlateCategory_tbl pcat)<> @plateCategory
select PCID,PC,PSID from  PlateCategory_tbl where Deleted=0
else
raiserror('2nd return',16,1)

answered Jun 24, 2014 at 10:46

Azar

1,86715 silver badges18 bronze badges

2 Comments

Azar Over a year ago

Jst to know whether if statement returned values or not

Azar Over a year ago

U can but this is like a message which is helpful for you as you said that you want to know whether it is returning rows in first and second if statements.

Collectives™ on Stack Overflow

get multiple return value in stored procedure in sql server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related