Problem returning OUTPUT parameter

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem returning OUTPUT parameter

Author

Topic

Miss Chez
Starting Member

4 Posts

Posted - 2004-07-13 : 06:10:54

Hi,
This code is driving me mad!!

The procedure is supposed to OUTPUT the @AssmtAttemptID of either an existing record or of a new record just inserted by the procedure. The @AssmtAttemptID is returned if there was an existing record BUT not if a new one is inserted. The @AssmtAttemptID is assigned the new record ID as it is used to insert the new record.

Why is it not being returned?

Please help!! I have been stumped over this for a few days now!!

CREATE PROCEDURE dbo.prInsertAssmtAttempt
(
	@AssmtInstID int,
	@AttemptNo int,
	@LastUpdateBy int,
	@AuditTypeID int,
	@AuditIP varchar(16),
	@AssmtAttemptID int OUTPUT
)
AS
	SELECT @AssmtAttemptID = AssmtAttemptID
	FROM tblAssmtAttempt
	WHERE tblAssmtAttempt.AssmtInstID = @AssmtInstID
	AND tblAssmtAttempt.AttemptNo = @AttemptNo

	IF @@ROWCOUNT = 0
	BEGIN
		SELECT @AssmtAttemptID = ISNULL(MAX(AssmtAttemptID),0) + 1 FROM tblAssmtAttempt
		INSERT INTO tblAssmtAttempt (AssmtAttemptID, AssmtInstID, AttemptNo, CreatedDate, LastUpdateBy, LastUpdated, AuditVersion, AuditCurrent, AuditTypeID, AuditIP)
		VALUES (@AssmtAttemptID, @AssmtInstID, @AttemptNo, GetDate(), @LastUpdateBy, GetDate(), 1, 1, @AuditTypeID, @AuditIP)
	END
GO

Any help is much appreciated.
Thanks (in advance),
Miss Chez

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-13 : 06:37:03

The first row of code inside the if block should look something like this:

SELECT ISNULL((SELECT MAX(AssmtAttemptID) FROM tblAssmtAttempt), 0) + 1

Duane.

Miss Chez
Starting Member

4 Posts

Posted - 2004-07-13 : 07:03:21

Hi Duane,

Thanks for your reply!

I tried changing the code as you suggested but it did the same. The new record was inserted but no OUTPUT was returned.

Any more thoughts?

Miss Chez

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-13 : 07:25:17

Hi,
You also need to declare your output parameter with 2 '@''s

eg: @@AssmtAttemptID int OUTPUT

when you call the proc you must also declare a variable simarlily and specify output at the output parameter

eg:
CREATE procedure test(@in int, @@test int output)
as
select @in
if @in = 100
begin
set @@test = 101
end

go

declare @@a int
exec test 100, @@a Output
select @@a

Duane.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-13 : 07:51:03

How are you calling this stored proc ? Post the calling code also.

Miss Chez
Starting Member

4 Posts

Posted - 2004-07-13 : 07:52:46

Hello Duane,

No joy!

Why 2 @'s? I tried it and it made no difference!

I have declared a variable in my ASP and assigned the OUTPUT to it. Like I said before, the OUTPUT is correct when there is an existing record.

Thanks,
Miss Chez

Miss Chez
Starting Member

4 Posts

Posted - 2004-07-13 : 08:01:13

Hi,

I am calling the SP from an ASP page as follows ....

'insert assessment attempt into tblAssmtAttempt (return AssmtAttemptID)
Set objAAConn = Server.CreateObject("ADODB.Connection")
objAAConn.Open Application("strConnWrite")
Set objAACmd = Server.CreateObject("ADODB.Command")
objAACmd.ActiveConnection = objAAConn
objAACmd.CommandText = "prInsertAssmtAttempt"
objAACmd.CommandType = &H0004      ' = adCmdStoredProc
objAACmd.Parameters.Refresh
Set objAARs = objAACmd.Execute(, Array(,intAssmtInstID, intAttemptID, numPersonID, 4, Request.ServerVariables("REMOTE_ADDR")))
					
intAssmtAttemptID = objAACmd.Parameters("@AssmtAttemptID")
					
Response.Write "intAssmtAttemptID is " & intAssmtAttemptID & "<br>"

Thanks,
Miss Chez

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-13 : 08:04:27

Stabbing in the dark

Try


CREATE PROCEDURE dbo.prInsertAssmtAttempt
(
	@AssmtInstID int,
	@AttemptNo int,
	@LastUpdateBy int,
	@AuditTypeID int,
	@AuditIP varchar(16),
	@AssmtAttemptID int OUTPUT
)
AS
	IF NOT EXISTS (SELECT 1 FROM tblAssmtAttempt WHERE tblAssmtAttempt.AssmtInstID = @AssmtInstID AND tblAssmtAttempt.AttemptNo = @AttemptNo)

	BEGIN
		SELECT @AssmtAttemptID = ISNULL(MAX(AssmtAttemptID),0) + 1 FROM tblAssmtAttempt
		INSERT INTO tblAssmtAttempt (AssmtAttemptID, AssmtInstID, AttemptNo, CreatedDate, LastUpdateBy, LastUpdated, AuditVersion, AuditCurrent, AuditTypeID, AuditIP)
		VALUES (@AssmtAttemptID, @AssmtInstID, @AttemptNo, GetDate(), @LastUpdateBy, GetDate(), 1, 1, @AuditTypeID, @AuditIP)
	END
	SELECT @AssmtAttemptID = AssmtAttemptID
	FROM tblAssmtAttempt
	WHERE tblAssmtAttempt.AssmtInstID = @AssmtInstID
	AND tblAssmtAttempt.AttemptNo = @AttemptNo
GO

I'm wondering if it's something to do with assigning the value inside the BEGIN/END block, can't see why that would be, but can't see any other reason why it would fail. Doing it this way (above), means that the last thing you do is get your output value, regardless of the INSERT...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-13 : 09:13:30

two things:

1) are you sure data is being returned based on the parameters you are passing in?

2) does calling the procedure from Query Analyzer set the output variable the way it should? you need to determine at what layer this problem is occurring -- T-SQL or ADO.

I've never heard of the need to use @@ for output parameters -- I never have.

- Jeff

ze
Starting Member

5 Posts

Posted - 2004-07-13 : 16:11:33

I tested his code with @ and @@ both worked!
I have always seen using @@ for reservered sql variables, like @@version or @@error.

ze

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources