UPDATE SQL SERVER Table procedure

[LTusing]

I have copied the following code for updating/editing sql server 2000 table data, which has errors that I do not understand how to resolve. I would greatly appreciate guidance.

CREATE PROCEDURE procConsentUpdate(
@icd_id int = NULL,
@pt_id int = NULL,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(150) = NULL OUTPUT)
AS
DECLARE @Err int
DECLARE @Exists bit
DECLARE @strSQL nvarchar(200)
DECLARE @ConsentCount int
SET NOCOUNT ON

/* Check if icd exists. */

IF @icd_id IS NULL
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'icd_id required.'
RETURN
END

--Bail if record does not exist
IF
(SELECT COUNT(*) FROM Consent
WHERE icd_id = @icd_id) = 0
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'icd_id does not exist.'
RETURN
END

SELECT @strSQL = N'UPDATE CONSENTSET '

--Bail if nothing to update
IF @Exists = 0
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'Nothing to do.'
RETURN
END

--Strip the trailing comma
SELECT @strSQL=LEFT ( @strSQL, LEN (@strSQL) -1)

--Add the WHERE clause
SELECT @strSQL = @strSQL +
' WHERE icd_id = ' + CAST (@icd_id AS int(4))

--Generate a plan
EXECUTE sp_executesql @strSQL

--If error, branch to error handler
SELECT @Err = @@ERROR
IF (@Err <> 0) GOTO HandleErr

--If we got this far, then no error
SELECT @RetCode = 1,
@RetMsg = 'Record Edited'
RETURN

HandleErr:
SELECT @icd_id = 0,
@RetCode = 0,
@RetMsg = 'Runtime Error: ' + CAST(@Err AS varchar(10))
RETURN
GO

 

[mrdenny]

What errors are you getting?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LTusing]

The enterprise manager states that the syntax is acceptable. However, I have a cmd procedure in VB that calls the proc_ConsentUpdate from an Access, 2003, Form (.adp). The table has the following fields: icd_id, pt_id, vdt_con, cons_dt, vdt_hip1, hipa_dt. I wish for the procedure to update 1 or mor of the column values. I am receiving the following error message, "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".

Thank you, -lt