Stored Procedure/Multiple Return Results/Set Statement

Stored Procedure/Multiple Return Results/Set Statement

Post by Bryan Reynold » Mon, 25 Mar 2002 03:51:36



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

 
 
 

Stored Procedure/Multiple Return Results/Set Statement

Post by Steve Kas » Mon, 25 Mar 2002 04:08:48


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



 
 
 

Stored Procedure/Multiple Return Results/Set Statement

Post by Bryan Reynold » Mon, 25 Mar 2002 04:13:59


I till try this!

Thanks for the quick response!

Bryan


> 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


 
 
 

Stored Procedure/Multiple Return Results/Set Statement

Post by Rob Nicholso » Mon, 25 Mar 2002 04:14:53


> 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

 
 
 

Stored Procedure/Multiple Return Results/Set Statement

Post by Narayana Vyas Kondredd » Mon, 25 Mar 2002 04:27:39


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

 
 
 

Stored Procedure/Multiple Return Results/Set Statement

Post by Umachandar Jayachandra » Mon, 25 Mar 2002 05:04:34


    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. )

 
 
 

1. return multiple result sets from a stored procedure

Does SQL only pass back once recordset from a stored procedure?

I would like to return multiple recordsets into ADODB and use the
Recordset.NextRecordset method to retrieve them into my application.

The syntax of Recordset.Open() shows multiple SQL statements, but I need the
return of multiple recordsets with the specification of only one SQL
statement(stored procedure).

My original design relied on passing a single parameter of dynamic criteria
and returning 8 recordsets... each one filtered with a temp table created
using the passed criteria.

Thanks.

2. Access Databases and VB3

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

4. Easy One

5. Returning Multiple Result Sets for Java Stored Procedures

6. Data grid totals

7. multiple result sets returned from a stored prodedure

8. qa guy needs to query db

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

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

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

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

13. Returning a result set from a stored procedure