Local variable unassigned after delete in a Stored Procedure
-
I have a stored procedure where I query for a value from a row in a table and put it into an OUTPUT variable. The next line I delete the row. When the stored procedure returns, it doesn't seem to have the value assigned from the row anymore. What am I missing?
CREATE PROCEDURE twrsp_ModifyLastDownloadTimestamp(
@last_download_time DATETIME OUTPUT,
@user_name VARCHAR(128) )
AS
BEGIN
IF EXISTS (SELECT 1 FROM tbl_remoteuser WHERE address = @user_name)
BEGIN
SELECT @last_download_time = time_sent FROM tbl_remoteuser WHERE address = @user_name
DELETE FROM tbl_remoteuser
WHERE address = @user_name
END
END;
go
Thank you for the hints. Another process calls this procedure and I do not have access to it. When I remove the DELETE statement, it returns the correct result. I shouldn't have multiple rows for a given @user_name, but I can watch better for that. If no rows exist for the @user_name, I do not want to modify the @last_download_time but leave it as the value that was passed into the procedure.
-
Please post the code you use to execute the stored procedure.
-
Few points...
1. Try commenting out your delete clause and see if your variable is still empty - if so, then it is not the delete (I doubt the delete is causing it)
2. What code are you using to call this? Make sure that you use the OUTPUT keyword in your calling code as well, or if using ADO,etc you'll need to set the parameter as an OUTPUT parameter.
eg
declare @last_download_time datetime, @username varchar(128)
set @username='xyz'
exec twrsp_ModifyLastDownloadTimestamp @last_download_time OUTPUT, @username
3. Do you intend anything to be returned in the output parameter if there are no rows in tbl_remoteuser?
4. I have usually stuck the output parameters at the end of the parameters list - I'm not sure if this is just some convention I stuck to a long time ago or if it is mandatory (never thought about it
) As a last resort, maybe swap the parameters?Cheers,
-
Make sure you call the proc in this manner:
Declare @OutParam datetime
--not mandatory
--set @OutParam = 'somedate'
exec dbo.twrsp_ModifyLastDownloadTimestamp @OutParam output, 'UserName'
Select @OutParam
-
This doesn't have anything to do with fixing the problem you described but I would like to suggest some modifications to the code.
Declare @rowcount int,
@error int
SELECT @last_download_time = time_sent
FROM tbl_remoteuser with (updlock)
WHERE address = @user_name
Select @rowcount = @@rowcount, @error = @@error
If @rowcount = 1
Begin
DELETE FROM tbl_remoteuser
WHERE address = @user_name
End
This eliminates the use of "IF EXISTS" which in this case just caused an unnecessary second read of the same row. Also, the updlock guarantees that no other process can modify the row between the time of your SELECT and DELETE.
Of course I only hinted at including proper error checking but that is a whole other topic.
Good Luck,
Ron
p.s. Your code doesn't address the issue that there may be more than one row with address = @user_name. So maybe it should be:
SELECT @last_download_time = Max(time_sent )
FROM tbl_remoteuser with (updlock)
WHERE address = @user_name
Select @rowcount = @@rowcount, @error = @@error
If @rowcount > 0
Begin
DELETE FROM tbl_remoteuser
WHERE address = @user_name
End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply