Simple problem in SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple problem in SP

Author

Topic

BuildHome
Starting Member

20 Posts

Posted - 2006-02-17 : 17:41:22

Hello,

I'm a beginner in Stored Procedures and I saw this 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:

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-17 : 17:49:37

>>trying to use a recordset that is closed.
This error is an application error not a sql error. So the problem is in your application code.

There is nothing wrong with your sql statements syntactically. (there is no WHERE clause so you will UDATE all rows to your variable values and then SELECT all rows back.

A good practice is to develop your sql code in Query Analyzer. Make sure it's working there, then call your (working) procedure from your application.

You can call your SP from query analyzer like this:


exec sp_name @name = 'TG'

Be One with the Optimizer
TG

BuildHome
Starting Member

20 Posts

Posted - 2006-02-17 : 17:58:13

I'm working with "Query Analyzer" (wrinting the SP & execute it) and in there it's working.

My application code is:

Set rs = Conn.Execute("exec usp_test '"&field1&"','"&field2&"'")
field1 = rs("field1")
field2 = rs("field2")

I don't see anything wrong here :\

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-17 : 18:20:12

Next thing to do is capture the sql that is being sent to the server using Sql Profiler. That way you can see exactly what (if anything) is being sent to sql server by your application.

this is a sql server forum not an asp forum, but aren't you supposed to check rs.EOF or something before you start using your record set? I don't see any error handling or reponse.write statements or anything to tell you what's going on.

Anyway, its friday night where i am and i'm going home. Good luck!

Be One with the Optimizer
TG

BuildHome
Starting Member

20 Posts

Posted - 2006-02-17 : 18:22:46

Yes, I have "If Not rs.EOF Then" but I didn't write it here.. :|

I don't know how to use the Profiler..I'll try...

Thank you,
BuildHome

P.S:
If someone that knows what the problem, please reply.

EDIT:
I've solved the problem. It was in ASP.
I've added ".NextRecordSet" to my "Set rs = Conn.Execute(...)" code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources