SQL Server - Cast column type in where clause

Asked By Anandh Ramanujam on 27-Jan-11 08:39 AM
Hi,


declare @sql varchar(max)

declare @val float

set @val=1000000

set @sql='select prgeo,prProID from prfmaPrePricingChecklistProduction where prrevacv < '+ @val

print @sql

exec (@sql)


Here, 'prrevacv' column is of type nvarchar. While executing this query, it reports

'Error converting data type varchar to float'

How to achieve this functionality. Is any way to convert the column 'prrevacv' to float and use that.

Please help me in this regard.

Robbe Morris replied to Anandh Ramanujam on 27-Jan-11 08:41 AM
cast(@val, varchar(50))

By the way, dynamic sql in a store procedure is still open to SQL Injection attacks.  Anytime you concatenate strings with parameter values of your stored proc, you are asking for trouble.