Using Exec to update table field values 1

[lpedde]

The following code is in a stored procedure in SQL 6.5. The table cnt1056 has the following structure.

chapter varchar(4),
New1yr int,
New1yrR money,
New2yr int,
New2yrR money,
etc...

I am trying to update the count and monetary rebate amount based on the type of membership that was paid for (new1yr, new2yr, etc).
The first exec("Update...@cnt) for the count works great, increasing the count by one each time.
The second exec for the rebate is not working. I am sure it is because @rebate is money and everything else are strings but I can't work around it. I'd appreciate any help I could get.

DECLARE @reb varchar(10),
@cnt varchar(10),
@header varchar(4),
@REBATE MONEY

select @reb = "New1yrR"
select @cnt = "New1yr"
select @header = "0044"
select @rebate = 1

exec("UPDATE cnt1056 SET "+@cnt+" = "+ @cnt + "+1 WHERE Chapter = '"+@Header+"'&quot

exec("UPDATE cnt1056 SET "+@reb+" = "+ @reb + "+"+@Rebate +" WHERE Chapter = '"+@Header+"'&quot

 

[jhall156]

I think you need a convert around the rebate

exec("UPDATE cnt1056 SET "+@reb+" = "+ @reb + "+"+CONVERT(varchar(20),@Rebate) +" WHERE Chapter = '"+@Header+"'&quot
JHall

 

[tlbroadbent]

Does the 2nd execute produce an error message or simply fail to update when executed?

I believe the problem is that the money value cannot be concatenated with the character strings within the SQL statement. Try this.

Declare @sql varchar(200)
Select @sql =
"UPDATE cnt1056 SET " +
@reb + " = " + @reb + "+" +
str(@Rebate) +
" WHERE Chapter = '" + @Header + "'"
exec(@sql)

I don't recall if SQL 6.5 allows functions in an Execute statement. SQL 2000 will not allow them. For that reason, I created the @sql variable. You may be able to enclose @rebate in the Str function within the execute statement. 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.

 

[lpedde]

Actually, both the convert and str functions cause a syntax error.

The original code produces an error:
incorrect syntax near '0044'
unclosed quote before the character string ' '.

From isolating the string in a select statement I believe the error involves the @rebate and not the @header part of the statement.

 

[lpedde]

Sorry tlbroadbent...you were right. When I first put the "Update..." into a @str variable then exec(@str) it works! Thanks.

 

[tlbroadbent]

Does the error occur when the functions are used in the execute statement? Did you try building a statement in a variable as I suggested? 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.

 

[tlbroadbent]

Sorry. Looks like I was creating my post when you made the last one. Thanks. 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.