CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user3252014
user3252014

Reputation: 665

get multiple return value in stored procedure in sql server

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?

Upvotes: 0

Views: 88

Answers (2)

Azar
Azar

Reputation: 1867

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)

Upvotes: 0

Azar
Azar

Reputation: 1867

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'

Upvotes: 1

Related Questions