Why can't I do an insert and a select in the same stored procedure

[bryant89]

I am trying to do an insert into a table and then do a select to retrieve the id. I have a field in the table set up to be the identifier but I can not select from it. The insert works fine but the select does not process. Is there something I am doing wrong. Or some other factor with SQL SERVER that I need to be checking in order to do an insert and a select in the same procedure.

My stored procedure looks like this:

Alter Procedure stpCreateReturn
(
@copName varchar(70),
@copCity varchar(40),
@copProv char(2),
@copCountry varchar(30),
@copAddress varchar(70),
@copStatus varchar(70),
@copEstCost money
)
AS

INSERT INTO tblContProj (copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)

SELECT @@IDENTIFIER

return


So the insert works and puts the values into the table. copID is the name of the field that is the IDENTITY set to increment by 1 everytime a record is inserted but is not returned. The results of this query are this:


Running dbo."stpCreateReturn" ( @copName = test, @copCity = test, @copProv = te, @copCountry = test, @copAddress = test, @copStatus = test, @copEstCost = $1000 ).

Finished running dbo."stpCreateReturn".



the select should follow this but does not.
I am testing this in interdev.

Any help would be appreciated.

Bryant Moore

 

[foxdev]

SELECT @@IDENTIFIER

That could be one problem: your line should read:

[tt]SELECT @@IDENTITY[/tt]

Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[bryant89]

oops I copied it wrong. In my code it is set to

SELECT @@IDENTITY


The insert will work but the IDENTITY is not selected.
I have even tried other selects such as:

Alter Procedure stpCreateReturn
(
@copName varchar(70),
@copCity varchar(40),
@copProv char(2),
@copCountry varchar(30),
@copAddress varchar(70),
@copStatus varchar(70),
@copEstCost money
)
AS

INSERT INTO tblContProj (copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)

SELECT copID
FROM tblContProj
WHERE copName = @copName
AND copCity = @copCity
AND copProv = @copProv
AND copCountry = @copCountry
AND copAddress = @copAddress
AND copStatus = @copStatus
AND copEstCost = @copEstCost

return

 

[bryant89]

I got it to work
I had to use this

Alter Procedure stpCreateReturn
(
@copName varchar(70),
@copCity varchar(40),
@copProv char(2),
@copCountry varchar(30),
@copAddress varchar(70),
@copStatus varchar(70),
@copEstCost money
)
AS
set nocount on
INSERT INTO tblContProj (copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)


SELECT @@IDENTITY
return

for some reason I had to put set nocount on before the insert and then it worked.

 

[foxdev]

I don't use InterDev, but probably what was happening is that the Insert caused a "1 row affected" type message getting returned, and it could only handle one return set, thus ignoring the second return set (select @@identity).
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[bryant89]

Thanks Robert.