SELECT won't work AFTER INSERT in SP

[jfradley]

I have a very simple SP procedure that will return the ID of a row if it exists and if it doesn't will insert it then try to select it again. I call this stored procedure from a C# program. The problem is if I have to INSERT then SELECT it doesn't return any rows, (but still inserts) but if I just have to SELECT it works fine. Any ideas?

ALTER PROCEDURE dbo.ICS_SP_INSERT_PLANT

(
@PLANT_NAME VARCHAR(50)
)

AS
SELECT plant_id
from ICS_PLANT_T
where plant_name_vc = @PLANT_NAME

IF @@RowCount <= 0
BEGIN

INSERT INTO ICS_Plant_T (Plant_Name_VC) values (@PLANT_NAME)

SELECT @@Identity as plant_id

END

 

[Craig0201]

Use a return paramater and return @@IDENTITY.

Craig

 

[jfradley]

Thank you, that worked.

 

[SQLSister]

Don't use @@Identity if you will have multiple users. What you want is scope_identity() as this will return the identity last issued from within your scope not the last one issued anywhere!