Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Unanswered: Simple problem in SP

    Hello,

    I'm a beginner in Stored Procedures and I saw this code:
    Code:
    CREATE PROCEDURE sp_name 
    @name nvarchar(50)
    
    AS
    
    DECLARE @id INT
    SET @id=(SELECT [ID] FROM tbl WHERE [Name]=@name)
    
    IF @id IS NULL
    BEGIN
        BEGIN TRANSACTION
        INSERT INTO tbl ([Name]) VALUES (@name)
        SET @id=(SELECT @@IDENTITY)
        COMMIT TRANSACTION
    END
    ELSE
        UPDATE tbl SET [Name]=@name WHERE [ID]=@id
    
    SELECT @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:
    Code:
    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.

    How can I make it work?

    Thanking you in advance,
    BuildHome

  2. #2
    Join Date
    Feb 2006
    Posts
    9
    Sorry for the double post but I've solved the problem. It was in ASP.
    I've added ".NextRecordSet" to my "Set rs = Conn.Execute(...)" code

  3. #3
    Join Date
    Oct 2003
    Posts
    17

    Asking questions...

    Hey,

    It would be great to post the solution to your problem if you found it elsewhere.

    It does not look too good for a person to go through your post ...

    Thanks,
    Tech_Punter

    Helping SQL community since 1998.

  4. #4
    Join Date
    Feb 2006
    Posts
    9
    My code was like this:

    Set rs = Conn.Execute("exec usp_test")

    I've added ".NextRecordSet" to the end of the row and now it's like this:

    Set rs = Conn.Execute("exec usp_test").NextRecordSet

    That's the solution

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •