Stored Procedure/Multiple Return Results/Set Statement 
Author Message
 Stored Procedure/Multiple Return Results/Set Statement

Hopefully this is an easy question.

Question:
Is there a way to suppress the return of a result set from
the stored procedure level?  Maybe I can solve this with a
set command or something else.  

The reason I as is because I made a stored procedure that
checks to see if the record exists in a table, if it does
exist I update it, if it does not exists, I insert it.  

The problem with this stored procedure is that it returns
two result sets. It returns the first one that I use to
check to see if the data already exists (This is the one I
don't want).  I then return the one that I do want which
should contain the record that I either added or updated.

When I run this code in my project and return a result set
it returns the first select statement.  Can I suppress
this statement using some Transact SQL command?  Or will I
have to loop thru and get both result sets?

ALTER PROCEDURE dbo.SP_tblObject_Change
        (









        )
        AS

SET NOCOUNT ON

SELECT * FROM tblObject WHERE


BEGIN
INSERT INTO tblObject(
        Domain_ID,
        Name,
        Type_ID,
        Date_Created,
        Date_Modified,
        Creator_ID,
        Modifier_ID,
        OLDID

        )

        Values (








        )
END

ELSE

BEGIN
UPDATE tblObject
        SET









WHERE


END

SET NOCOUNT OFF
SELECT * FROM tblObject WHERE



Thu, 09 Sep 2004 02:51:36 GMT
 Stored Procedure/Multiple Return Results/Set Statement

Bryan,

  I think you can simply change

SELECT * FROM tblObject WHERE


to

IF EXISTS (
  SELECT * FROM tblObject

to avoid a result set from the check.

Steve Kass
Drew University

Quote:

> Hopefully this is an easy question.

> Question:
> Is there a way to suppress the return of a result set from
> the stored procedure level?  Maybe I can solve this with a
> set command or something else.

> The reason I as is because I made a stored procedure that
> checks to see if the record exists in a table, if it does
> exist I update it, if it does not exists, I insert it.

> The problem with this stored procedure is that it returns
> two result sets. It returns the first one that I use to
> check to see if the data already exists (This is the one I
> don't want).  I then return the one that I do want which
> should contain the record that I either added or updated.

> When I run this code in my project and return a result set
> it returns the first select statement.  Can I suppress
> this statement using some Transact SQL command?  Or will I
> have to loop thru and get both result sets?

> ALTER PROCEDURE dbo.SP_tblObject_Change
>         (









>         )
>         AS

> SET NOCOUNT ON

> SELECT * FROM tblObject WHERE


> BEGIN
> INSERT INTO tblObject(
>         Domain_ID,
>         Name,
>         Type_ID,
>         Date_Created,
>         Date_Modified,
>         Creator_ID,
>         Modifier_ID,
>         OLDID

>         )

>         Values (








>         )
> END

> ELSE

> BEGIN
> UPDATE tblObject
>         SET









> WHERE


> END

> SET NOCOUNT OFF
> SELECT * FROM tblObject WHERE




Thu, 09 Sep 2004 03:08:48 GMT
 Stored Procedure/Multiple Return Results/Set Statement
I till try this!

Thanks for the quick response!

Bryan


Quote:
> Bryan,

>   I think you can simply change

> SELECT * FROM tblObject WHERE


> to

> IF EXISTS (
>   SELECT * FROM tblObject

> to avoid a result set from the check.

> Steve Kass
> Drew University


> > Hopefully this is an easy question.

> > Question:
> > Is there a way to suppress the return of a result set from
> > the stored procedure level?  Maybe I can solve this with a
> > set command or something else.

> > The reason I as is because I made a stored procedure that
> > checks to see if the record exists in a table, if it does
> > exist I update it, if it does not exists, I insert it.

> > The problem with this stored procedure is that it returns
> > two result sets. It returns the first one that I use to
> > check to see if the data already exists (This is the one I
> > don't want).  I then return the one that I do want which
> > should contain the record that I either added or updated.

> > When I run this code in my project and return a result set
> > it returns the first select statement.  Can I suppress
> > this statement using some Transact SQL command?  Or will I
> > have to loop thru and get both result sets?

> > ALTER PROCEDURE dbo.SP_tblObject_Change
> >         (









> >         )
> >         AS

> > SET NOCOUNT ON

> > SELECT * FROM tblObject WHERE


> > BEGIN
> > INSERT INTO tblObject(
> >         Domain_ID,
> >         Name,
> >         Type_ID,
> >         Date_Created,
> >         Date_Modified,
> >         Creator_ID,
> >         Modifier_ID,
> >         OLDID

> >         )

> >         Values (








> >         )
> > END

> > ELSE

> > BEGIN
> > UPDATE tblObject
> >         SET









> > WHERE


> > END

> > SET NOCOUNT OFF
> > SELECT * FROM tblObject WHERE




Thu, 09 Sep 2004 03:13:59 GMT
 Stored Procedure/Multiple Return Results/Set Statement

Quote:
> SELECT * FROM tblObject WHERE



Two choices here.

First, use a temporary table which won't return a recordset:



ELSE
ENDIF
DROP TABLE #TEMP1

or a far neater solution is to include the select in the IF line itself:


ELSE
ENDIF

Count(*) returns a count of the number of records in the select but the
select is thrown away.

Cheers, Rob



Thu, 09 Sep 2004 03:14:53 GMT
 Stored Procedure/Multiple Return Results/Set Statement
Two options:

1) Use IF EXISTS. Example:

IF EXISTS
(
SELECT 1 FROM tblObject WHERE

)
BEGIN
    --Update
END
ELSE
BEGIN
    --INSERT
END


row.
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Hopefully this is an easy question.

Question:
Is there a way to suppress the return of a result set from
the stored procedure level?  Maybe I can solve this with a
set command or something else.

The reason I as is because I made a stored procedure that
checks to see if the record exists in a table, if it does
exist I update it, if it does not exists, I insert it.

The problem with this stored procedure is that it returns
two result sets. It returns the first one that I use to
check to see if the data already exists (This is the one I
don't want).  I then return the one that I do want which
should contain the record that I either added or updated.

When I run this code in my project and return a result set
it returns the first select statement.  Can I suppress
this statement using some Transact SQL command?  Or will I
have to loop thru and get both result sets?

ALTER PROCEDURE dbo.SP_tblObject_Change

)
AS

SET NOCOUNT ON

SELECT * FROM tblObject WHERE


BEGIN
INSERT INTO tblObject(
Domain_ID,
Name,
Type_ID,
Date_Created,
Date_Modified,
Creator_ID,
Modifier_ID,
OLDID

)


)
END

ELSE

BEGIN
UPDATE tblObject
SET









WHERE


END

SET NOCOUNT OFF
SELECT * FROM tblObject WHERE



Thu, 09 Sep 2004 03:27:39 GMT
 Stored Procedure/Multiple Return Results/Set Statement
    There is no need to check for the presence of row actually. You can just
do:

update tblObject
   set ....


begin
    insert into tblObject...

end

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Thu, 09 Sep 2004 04:04:34 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. return multiple result sets from a stored procedure

2. How not to return multiple result sets from a stored procedure

3. Returning Multiple Result Sets for Java Stored Procedures

4. multiple result sets returned from a stored prodedure

5. Can't update result set returned from stored procedure using RDO

6. OLE ,Oracle, ans Stored Procedures returning result sets

7. Executing Stored Procedure that returns result set (in Kiva)

8. Can't update result set returned from stored procedure using RDO

9. Returning a result set from a stored procedure

10. Result Set returned from a Stored Procedure

11. Returning result set from function to stored procedure is very slooooooooow

12. returning result sets from stored procedures


 
Powered by phpBB® Forum Software