Help with the Stored Proc
-
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
-
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 -
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. -
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/ -
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.
-
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 -
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 -
yep! This is what I m trying for ! Thank you
Sorry if i was not clear in my previous posts!!
-
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 workselect 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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply