Dynamic UPDATE Stored Procedure

[sjuarez1979]

This is what I put in:

CREATE PROCEDURE wo_EditContact
@ContactID int,
@FirstName char(10),
@LastName char(10),
@Address1 char(10),
@Address2 char(10),
@City char(10),
@State char(10),
@Zip int,
@Email char(50),
@PhoneNumber char(20),
@FAX char(20),
@Extension char(10),
@GroupName char(10),
@Username char(50),
@UsrPassword char(50),
@Retired bit
AS
DECLARE @NowDate datetime
SELECT @NowDate = GetDate()

DECLARE @SQLQuery varchar(255)
SELECT @SQLQuery =
"UPDATE tbl_Contact
SET
FirstName = '" + RTRIM(@FirstName) + "',
LastName = '" + RTRIM(@LastName) + "',
Address1 = '" + RTRIM(@Address1) + "',
Address2 = '" + RTRIM(@Address2) + "',
City = '" + RTRIM(@City) + "',
State = '" + RTRIM(@State) + "',
Zip = " + @Zip + ",
PhoneNumber = '" + RTRIM(@PhoneNumber) + "',
FAX = '" + RTRIM(@FAX) + "',
Extension = '" + RTRIM(@Extension) + "',
Email = '" + RTRIM(@Email) + "',
GroupName = '" + RTRIM(@GroupName) + "',
Username = '" + RTRIM(@Username) + "',
UsrPassword = '" + RTRIM(@UsrPassword) + "',
ContactRetired = " + @Retired + ",
RetireDate = " + @NowDate + "
WHERE ContactID = " + @ContactID
EXEC(@SQLQuery)
GO

This is what it says:

Server: Msg 245, Level 16, State 1, Procedure wo_EditContact, Line 34
Syntax error converting the varchar value 'UPDATE tbl_Contact
SET
FirstName = 'Steve',
LastName = 'Uglyint',
Address1 = '4555 S. Va',
Address2 = 'ok',
City = 'Henderson',
State = 'NV',
Zip = ' to a column of data type int.

Does anyone know how this can be corrected???
-sjuarez1979

 

[RobertT687]

What is the data type of Zip in tbl_Contact? If it is chatacter, you must convert the value of @Zip to Character Explicitly. Such as :

Zip = " + Convert(Char(9),@Zip) + ",

Additionally why go to the trouble of building an SQL command string to execute?
Instead, use:
UPDATE tbl_Contact
SET
FirstName = RTRIM(@FirstName),
LastName = RTRIM(@LastName),
Address1 = RTRIM(@Address1),
Address2 = RTRIM(@Address2),
City = RTRIM(@City),
State = RTRIM(@State),
Zip = CONVERT(char(9), @Zip),
PhoneNumber = RTRIM(@PhoneNumber),
FAX = RTRIM(@FAX),
Extension = RTRIM(@Extension),
Email = RTRIM(@Email),
GroupName = RTRIM(@GroupName),
Username = RTRIM(@Username),
UsrPassword = RTRIM(@UsrPassword),
ContactRetired = @Retired,
RetireDate = @NowDate,
WHERE ContactID = @ContactID

You can also declare the procedure input variables as Varchar instead of Char and forego the RTRIM exercise since Varchar datatype discards trailing spaces anyway.

 

[Andel]

Your @zip variable is an Integer. And you're concatinating it with a char. You need to change your @zip variable to a varchar then it should be fine.
Andel
andel@barroga.net

http://andyb.8m.com

 

[Andel]

RobertT687,

It should not matter what data type the zip in the tbl_contact. The SELECT @SQLQuery = .... already have a syntax because it's concatinating varchar with integer and you can't do that in sql.


Andel
andel@barroga.net

http://andyb.8m.com

 

[sjuarez1979]

I guess I was confused by an article found @

http://www.4guysfromrolla.com/webtech/020600-1.shtml

which discusses dynamic stored procedures. Is this a deprecated article? RoberT687, your update procedure works perfectly. Any comments on the article about stored procedures?

 

[tlbroadbent]

There are uses for dynamic SQL. As you can see, it was not needed to perform your update. However, if column or table names are passed to the procedure as parameters, you will need to build and execute dynamic SQL. SQL can't use a variable for an object or column name.

There are other circumstances when dynamic SQL is useful and necessary. However, it should only be used when necessary because of its shortcomings and the difficulty of debugging. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.