only return one recordset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

only return one recordset

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-07-27 : 12:36:31

I have a simple stored procedure that first checks for the existence of a record before returning a result set. I only want the last two result sets returned, not the first one that checks for the record. I thought setting nocount would do this. How do I do this?

ALTER PROCEDURE producer_get_finance
@producer int
,@company char(1)
,@subcompany char(1)
AS
BEGIN

SET NOCOUNT ON;

SELECT 1
FROM producer_finance_map
WHERE producer = @producer
AND company = @company
AND subcompany = @subcompany

IF @@ROWCOUNT > 0
BEGIN
SET NOCOUNT OFF
SELECT *
FROM finance
WHERE finance = 134
END
ELSE
BEGIN
SET NOCOUNT OFF
SELECT *
FROM finance
WHERE status = 'A'
AND finance in (
SELECT finance
FROM finance_state
WHERE state = 'FL'
AND company = '2'
AND subcompany= '2'
)
ORDER BY Name

END
END
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-27 : 13:21:06

"set nocount" only affects whether or not the "(n rows(s) affected)" message is returned or not. It has no affect on your conditional logic. If you want to perform conditional statements based on the existence of a rows here are two possibilites:

you can either do this:


if exists (<statement>)
begin
       <if exists statement>
end
else
begin
       <if not exists statement>
end

or this:


<statement>

if @@rowcount = 0
begin
       <alternate statement if no rows were returned from first statement>
end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources