Dynamic SQL and Integer Error - 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 2000 Forums

SQL Server Development (2000)

Dynamic SQL and Integer Error

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-09-15 : 13:54:09

I am getting an error because I am trying to do a lookup on an INT column when the dynamic query submitted is a string. How can I fix?

Thanks

[error]
Syntax error converting the varchar value 'update transcripts set Title = A Skeptic s Search—Part 1 where TranscriptsID = ' to a column of data type int.
[/error]


Declare @ColumnName VarChar(1000)
Declare @IncomingText VarChar(1000)
Declare @intAutoID int
Declare @SQL VarChar(5000)

set @ColumnName = 'Title'
set @IncomingText = 'A Skeptic s Search—Part 1'
set @intAutoID = 239

SELECT @SQL = 'update transcripts ' 
SELECT @SQL = @SQL + 'set ' + @ColumnName + ' = ' + @IncomingText + ' where TranscriptsID = ' + @intAutoID

Alex Polajenko

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-15 : 13:56:07

Convert the value to VARCHAR.

SELECT @SQL = @SQL + 'set ' + @ColumnName + ' = ' + @IncomingText + ' where TranscriptsID = ' + CONVERT(VARCHAR(50), @intAutoID)

The problem is occurring because @SQL is VARCHAR, but @intAutoID is not. So you need to convert it.

Tara

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-09-15 : 14:09:41

thanks but actually you helped me see that I had to add more quotes around the @IncomingText and it worked!
thanks again


SELECT @SQL = 'update transcripts ' 
SELECT @SQL = @SQL + 'set ' + @ColumnName + ' = ''' + @IncomingText + ''' where TranscriptsID = ' + cast(@intAutoID as varchar)

Alex Polajenko

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources