Help with the Stored Proc

Question

Post reply

Help with the Stored Proc

ssismaddy

SSCarpal Tunnel

Points: 4850
More actions
November 9, 2010 at 9:40 am

#231218

Hi, I have a strored proc which gives the following output :-
ServerId day Status
1 1 Success
2 2 Success
3 3 Failed
4 4 Success
5 5 Success
I was asked to design another proc which should give the output Success/Failed. Like, If there is any status with Failed, the stored proc should return only Failed. Otherwise Success should be the output. Can someone throw me an idea how I can get this!!
Thanks

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

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

select top 1 status from yourtable where status='Failure'

IF @@rowcount>=1

select 'Failed'

else

select 'Success'

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

nigelrivett SSCertifiable Points: 5472 More actions · Answer 2

Easiest is to look at the SP.

It will have a select statement at the end

select ServerId, day, Status

from ...

....

change this to

if exists (

select *

from ...

....

where status = 'Failed')

begin

select result = 'Failed'

end

else

begin

select result = 'Succeeded'

end

Another option

select ServerId, day, Status

into #a

from ...

....

if exists (select * from #a where status = 'Failed')

begin

select result = 'Failed'

end

else

begin

select result = 'Succeeded'

end

Cursors never.
DTS - only when needed and never to control.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Since you didn't show us the table and proc's code, I can only assume that something like the code bellow would work

select min(status) from...

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 4

Thank you all for the quick replies. I know the table which gives the output. But my concern is, I am not supposed to access the table directly, I have to call this stored proc and from the output I have to generate new output failed or success.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

That wont be much of a problem.any of the above solutions posted will work.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

WayneS SSC Guru Points: 95460 More actions · Answer 6

ssismaddy (11/9/2010)
Thank you all for the quick replies. I know the table which gives the output. But my concern is, I am not supposed to access the table directly, I have to call this stored proc and from the output I have to generate new output failed or success.

In that case, you'll have to dump the output of the stored procedure into a table, and then select off of that. This will probably do what you need:

DECLARE @SPTable TABLE (ServerId INT, [day] INT, [Status] varchar(7));

INSERT INTO @SPTable EXEC dbo.YourSP;

DECLARE @result varchar(7);

SELECT @result = MIN([Status]) FROM @SPTable;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 7

yep! This is what I m trying for ! Thank you

Sorry if i was not clear in my previous posts!!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Adi Cohn-120898 (11/9/2010)
Since you didn't show us the table and proc's code, I can only assume that something like the code bellow would work
select min(status) from...
Adi

I love simple! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)