Re: Error msg

Roy (Roy@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> The stored procedures looks like this:
> CREATE PROCEDURE sp
> @ID int, @Parameter ...
> AS
> SET NOCOUNT ON
> SELECT * FROM table
> WHERE ID = @ID
> IF (@@ROWCOUNT <> 0)
> BEGIN
> // update record table here
> END
> ELSE
> BEGIN
> // add record to table here
> END
> RETURN @@ERROR
> GO
>
> These parameters are used to add/update records. Will they generate
> unconsumed result sets as you said?

"SELECT * FROM table" generates a result set which you need to process.
But if all you want to do is to check for existence, you should do:

IF NOT EXISTS (SELECT * FROM tbl WHERE id = @id)
BEGIN
INSERT ....
END
ELSE
UPDATE ...
END

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Help with SQL 2005 and Sourcesafe 6
    ... That doesn't seem to be the case with stored procedures. ... compile that latest version to be table to use it. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: nested stored procedures and returning lots of rows
    ... I initially thought of using nested stored procedures and returning ... proc then the called stored procs can't see it ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Error msg
    ... > I have corrected my stored procedures with what you said. ... In an interface like ADO you would iterate over ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.data.oledb)
  • Re: Create user.....
    ... But if the applicaiton relies on stored procedures, ... all the user needs is EXEC permission on the procedures. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Select *
    ... work, but yeild incorrect results. ... If you are using stored procedures, you can use sysdepends to track ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)