CODEWITHSUNDEEP
sqlsql-servert-sql
apd23
apd23

Reputation: 13

Conversion failed when converting value in dynamic sql statement

I'm stuck on the following sql server:

DECLARE @sql AS NVARCHAR(500),@db as varchar(50),@value AS CHAR(129);
SET @db = 'SSCT1';
SET @value = '1806-11-801-1000';
SET @sql = 'SELECT ACTINDX FROM ' + quotename(@db) 
  + '.[dbo].[GL00105] WHERE ACTNUMST = ' + @value;
EXEC (@sql);

When I run this in sql server I get : Conversion failed when converting the varchar value '1806-11-801-1000

I checked the field I'm using the where clause against and it matches the type in the declaration (char(129) so I'm not sure what it's trying to convert.

I'm trying to build a sql statement that will accept the db name as a variable in addition to the value. any thoughts?

thanks

Upvotes: 1

Views: 976

Answers (3)

Michael
Michael

Reputation: 1

Does this fail when you try to execute the SQL? Or if you comment the EXEC call out, does it still fail?

One thing that catches my eye, is that you don't have any single quotes around your @value. So when the @sql is built, it will end up building it as ... 

WHERE ACTNUMST = 1806-11-801-1000

however, it should look like this...

WHERE ACTNUMST = '1806-11-801-1000'

If this is indeed your issue, then you should modify your SET @sql to the following...

SET @sql = 'SELECT ACTINDX FROM ' + quotename(@db) + '.[dbo].[GL00105] WHERE ACTNUMST = ''' + @value + '''';

This will add the single quotes to your dynamic sql statement. I hope this helps.

Upvotes: 0

codingbiz
codingbiz

Reputation: 26376

This could happen because you didn't quote the char value. Try this

SET @sql = 'SELECT ACTINDX FROM ' + quotename(@db) + '.[dbo].[GL00105] WHERE ACTNUMST = ''' + @value + '''';

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

I'm going to guess that ACTNUMST is a string column, in which case you need to delimit it correctly:

SET @sql = 'SELECT ACTINDX FROM ' + quotename(@db) 
  + '.[dbo].[GL00105] WHERE ACTNUMST = ''' 
  + @value + ''';';

If @value might ever contain apostrophes, you need to deal with that further:

SET @sql = 'SELECT ACTINDX FROM ' + quotename(@db) 
  + '.[dbo].[GL00105] WHERE ACTNUMST = ''' 
  + REPLACE(@value, '''', '''''') + ''';';

Yuck. A much safer approach is:

DECLARE 
  @sql   NVARCHAR(MAX),
  @db    SYSNAME,
  @value CHAR(129);

SELECT
  @db = N'SSCT1',
  @value = '1806-11-801-1000';

SET @sql = N'SELECT ACTINDX FROM ' + quotename(@db) 
  + '.[dbo].[GL00105] WHERE ACTNUMST = @val;';

EXEC sp_executesql @sql, N'@val CHAR(129)', @value;

This guards a little better against dynamic SQL and also prevents you from having to deal with the quotes.

Upvotes: 3

Related Questions