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