String Value in an Execute? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

String Value in an Execute?

Author

Topic

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 04:44:21

hi there guys sorry 4 the rather silly question i'm sure but we all start somewhere...

i have the following strored proc...

ALTER PROCEDURE [schBank].[spAddTransactionField]
@Value as VARCHAR(Max),
@FieldId as INT,
@TransactionId as INT,
@CampaignId as INT
AS
EXECUTE ('UPDATE schBank.TblLeads' + @CampaignId + ' SET [' + @FieldId + '] = ' + @Value + ' WHERE [TransactionId] = ' + @TransactionId)

now my silly question is the value is a varchar in a normal update id put it inside single quotes but it is in single quotes in the Execute and refered to it a a column.

Thanks guys

Vincent Fradnsen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-22 : 04:47:34

Not clear what you want, but avoid such kind of practices and try to come up with a good schema design to keep D-SQL away.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 04:53:48

use a string variable to form your query

set @sql ='UPDATE schBank.TblLeads' + @CampaignId + ' SET [' + @FieldId + '] = ' + @Value + ' WHERE [TransactionId] = ' + @TransactionId

PRINT @sql -- print it out and verify it

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 04:54:05

A Normal UPDATE would USE the folowing:

UPDATE schBank.TblLeads55547
SET [@FieldId] = '@@Value'
WHERE [TransactionId] = @TransactionId

to parse the "string" value into the record...

i cant put the '' in the execute? needs to be dynamic i know its not correct but i cant get around it... (been tring 4 weeks...)

Thank

Vincent Fradnsen

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 04:54:57

Sorry ignor the second @ in red...

Vincent Fradnsen

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 04:57:53

Hi KH that leave me with the same problem i still cant specify the @value as a string and not a coulumn name...

thanks

Vincent Fradnsen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 04:58:23

[code]
set @sql = 'UPDATE schBank.TblLeads' + convert(varchar(10), @CampaignId) +
' SET [' + convert(varchar(10), @FieldId) + '] = ' + @Value +
' WHERE [TransactionId] = ' + convert(varchar(10), @TransactionId)
exec (@sql)
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 05:00:32

your @CampaignId, @FieldID and @TransactionId are all integer value. So you need to convert to varchar before able to concatenate

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 05:10:02

the messages returns as follows

UPDATE schBank.TblLeads2 SET [2] = tetetet WHERE [TransactionId] = 1
Msg 203, Level 16, State 2, Procedure spAddTransactionField, Line 13
The name 'UPDATE schBank.TblLeads2 SET [2] = tetetet WHERE [TransactionId] = 1' is not a valid identifier.

code adapted to:
DECLARE @SQL as Varchar(max)
set @sql = 'UPDATE schBank.TblLeads' + convert(varchar(10), @CampaignId) +
' SET [' + convert(varchar(10), @FieldId) + '] = ' + @Value +
' WHERE [TransactionId] = ' + convert(varchar(10), @TransactionId)
PRINT @SQL
EXEC @sql

Vincent Fradnsen

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 05:11:50

quote:
Originally posted by VincentFrandsen

the messages returns as follows

UPDATE schBank.TblLeads2 SET [2] = tetetet WHERE [TransactionId] = 1
Msg 203, Level 16, State 2, Procedure spAddTransactionField, Line 13
The name 'UPDATE ...

that is still my problem i think?

Vincent Fradnsen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 05:13:50

@Value is a sting so you need to enclose it in single quote


set @sql = 'UPDATE schBank.TblLeads' + convert(varchar(10), @CampaignId) +
' SET [' + convert(varchar(10), @FieldId) + '] = ''' + @Value + ''''
' WHERE [TransactionId] = ' + convert(varchar(10), @TransactionId)

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 05:17:02

thanks KN.. just what i needed i'd only put one extra ' and it didnt work... APPr It

Vincent Fradnsen

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 05:33:07

FINAL CODE

DECLARE @SQL as Varchar(max)
set @sql = 'UPDATE schBank.TblLeads' + convert(varchar(10), @CampaignId) +
' SET [' + convert(varchar(10), @FieldId) + '] = ''' + @Value +
''' WHERE [TransactionId] = ' + convert(varchar(10), @TransactionId)
PRINT @SQL
EXECUTE ('' + @sql + '')

Vincent Fradnsen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 06:00:57

[code]
DECLARE @SQL as Varchar(max)
set @sql = 'UPDATE schBank.TblLeads' + convert(varchar(10), @CampaignId) +
' SET [' + convert(varchar(10), @FieldId) + '] = ''' + @Value +
''' WHERE [TransactionId] = ' + convert(varchar(10), @TransactionId)
PRINT @SQL
EXECUTE (@sql)
[/code]

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:06:02

That doenst work... its errror is it is not an identifyer?

that is why i put the quotes in the execute?

Vincent Fradnsen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 09:19:27

can you post the output of PRINT @SQL and also the error messages ?

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:45:10

I must of had a syntax error i created a temp proc again with your code exactly and it worked Fine ... SORRY :-)

Vincent Fradnsen

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:47:02

The Proc...

use std
go

Create Procedure schbank.TempUpdateFields
@CampaignId INT,
@FieldId INT,
@Value Varchar(MAX),
@TransactionId INT
AS

DECLARE @SQL as Varchar(max)
set @sql = 'UPDATE schBank.TblLeads' + convert(varchar(10), @CampaignId) +
' SET [' + convert(varchar(10), @FieldId) + '] = ''' + @Value +
''' WHERE [TransactionId] = ' + convert(varchar(10), @TransactionId)
PRINT @SQL
EXECUTE (@sql)

Message:

Command(s) completed successfully.

Cool I'm Happy Thanks....

Vincent Fradnsen

aliam
Starting Member

1 Post

Posted - 2007-04-04 : 01:22:33

I have the same problem but could not make it work with your solution. Here's my code:

DECLARE @GetColNm varchar (75)
DECLARE @Tprodname varchar(75)
DECLAre @SQL varchar (1000)

SET @GetColNm = 'AIFMoCt'
SET @tprodname = 'CCSG Charge'

Set @SQl = ( 'SELECT ' + @GetColNm + ' FROM BMetricB03A_table WHERE Tprodname = ' + ''+ @Tprodname + '')

When I print it:
SELECT AIFMoCt FROM PROJCNTL.Msimbu.LE_BMetricB03A_table WHERE Tprodname = CCSG Charge

CCSG charge should be in quotes in order to work.

Im really desperate now. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 01:23:42

[code]
Set @SQl = ( 'SELECT ' + @GetColNm + ' FROM BMetricB03A_table WHERE Tprodname = ''' + @Tprodname + '''')
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 03:14:58

1 Avoid passing object names as parameter
2 Read this fully www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources