I'm a beginner in Stored Procedures and I saw this code:
CREATE PROCEDURE sp_name
DECLARE @id INT
SET @id=(SELECT [ID] FROM tbl WHERE [Name]=@name)
IF @id IS NULL
INSERT INTO tbl ([Name]) VALUES (@name)
SET @id=(SELECT @@IDENTITY)
UPDATE tbl SET [Name]=@name WHERE [ID]=@id
I saw that in this SP there is an Insert/update query and than it returns the updated ID with a select query.
I tried more simple query that this and it doesn't work.
I'm getting the error of trying to use a recordset that is closed.
This is like my simple SP:
UPDATE tbl SET field1=@field1, field2=@field2
SELECT field1,field2 FROM tbl
I switch the queries order (select first and after that the update query) and it's working
but I must to have the update query first to update the fields and than to use the select query.