Re: Error msg

Erland,
I have corrected my stored procedures with what you said. But I still get
same error. Below is my stored procedure code:
CREATE PROCEDURE spAddTable1
@IID uniqueidentifier, @Name nchar(128)
AS
SET NOCOUNT ON
INSERT Table1 (IID, Name)
VALUES(@IID, @Name)
SET NOCOUNT OFF
GO

CREATE PROCEDURE spUpdateTable1
@IID uniqueidentifier, @Name nchar(128)
AS
SET NOCOUNT ON
UPDATE Table1
SET Name = @Name
WHERE IID = @IID
SET NOCOUNT OFF
GO

CREATE PROCEDURE spSetData
@IID uniqueidentifier, @Name nchar(128), @Num smallint
AS
SET NOCOUNT ON
DECLARE @return_status int
-- Check record, update if exist, add new if nonexist
IF NOT EXISTS(
SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.IID = Table2.IID
WHERE Table1.IID = @IID
)
BEGIN
EXEC @return_status = spAddTable1 @IID, @Name

INSERT Table2 (IID, Num)
VALUES (@IID, @Num)
END
ELSE
BEGIN
EXEC @return_status = spUpdateTable1 @IID, @Name

UPDATE Table2
SET Num = @Num
WHERE IID = @IID
END
SET NOCOUNT OFF

I called spSetData procedured and it will in turn call spSetTable1. I
checked my code and found that this is actually my first procedure call after
I open the session. This procedure is in a transaction. Can you find any
error here?
Note: Table1 has two fields (IID and Name) and Table2 has two fields (IID
and Num) and there is a one-one relation between them.
Thanks.

"Erland Sommarskog" wrote:

> 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: Stored procedure error handling
    ... i'm trying to do some error checking on stored procedures and am ... > DECLARE @newSupplierId as bigint ... > INSERT INTO Supplier ... > SET NOCOUNT OFF ...
    (comp.databases.ms-sqlserver)
  • Re: Updating sql05 with Stored Procedure
    ... Have you provided stored procedures for select/insert/update/delete? ... @Original_CategoryID int, ... SET NOCOUNT OFF; ... SR.lutCategories WHERE (CategoryID = SCOPE_IDENTITY()) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored procedure executes twice
    ... The SET NOCOUNT issue is an unrelated issue to yours (judging by your ... duplicate executions. ... >> Do your stored procedures contain SET NOCOUNT ON? ... >> used ADO); try changing the lock types to see if that helps. ...
    (microsoft.public.sqlserver.programming)
  • RE: What stored procedures call this stored procedure?
    ... The first one select the information from the system table SYSCOMMENTS (see ... I see no such maintained relationships in the system tables between ... > a stored procedure and other stored procedures. ... >> set nocount on ...
    (microsoft.public.sqlserver.programming)