Dynamic SQL and implicit conversion of data types

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 implicit conversion of data types

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-06 : 11:13:17

hello again

I have another issue:


DECLARE @handsetNumber VARCHAR(12)
DECLARE @sql VARCHAR(2000)
SET @handsetNumber = '07739592167'

SET @sql = ' SELECT * FROM Handset WHERE HandsetNumber = ' + @handsetNumber

EXEC (@SQL)

Error Message:


Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I have tried:


SET @sql = ' SELECT * FROM Handset WHERE HandsetNumber = ' + CONVERT(VARCHAR,@handsetNumber)

But get the same message. Please help.................

Hearty head pats

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 11:19:38

What datatype is HandsetNumber? If the second one fails I guess it's a varchar so

SET @sql = ' SELECT * FROM Handset WHERE HandsetNumber = ''' + CONVERT(VARCHAR,@handsetNumber) + ''''

The variable is a varchar so that must be it - no need for the convert either

SET @sql = ' SELECT * FROM Handset WHERE HandsetNumber = ''' + @handsetNumber + ''''

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-06 : 11:27:42

Thankyou very much! That works perfectly.

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources