Getting "Conversion Failed" Error in SQl Server

Question

Unknown error in Dynamic String Handling in a T-SQL Environment. PS: I am not going to do any conversions. Just want to create an Update statement and execute it later

Code:

DROP TABLE IF EXISTS #TEMP

Select 'CRDB_Reporting' tablename,'MagnitudePackage' updatecolumn,'String' updatecolumntype,'''XLFWH''' updatevalue,'NatureOfFundCode' primaryfilter,'String' primaryfiltertype,'''FWH''' primaryfiltervalue,NULL remainingfilter
INTO #TEMP

Select  
CASE 
WHEN remainingfilter IS NOT NULL THEN 
    'Update [dbo].[' + tablename +'] Set [' + updatecolumn +']  = ' + updatevalue + ' Where [' + primaryfilter +']  = ' + primaryfiltervalue + ' And ' + remainingfilter
WHEN remainingfilter IS NULL THEN   
    'Update [dbo].[' + tablename +'] Set [' + updatecolumn +']  = ' + updatevalue + ' Where [' + primaryfilter +']  = ' + primaryfiltervalue + ' And '
END
From #Temp

Expected Result

Update [dbo].[CRDB_Reporting] Set [MagnitudePackage] = 'XLFWH' Where [NatureOfFundCode] = 'FWH'

Error Msg:

Msg 245, Level 16, State 1, Line 34 Conversion failed when converting the varchar value 'Update [dbo].[CRDB_Reporting] Set [MagnitudePackage] = 'XLFWH' Where [NatureOfFundCode] = 'FWH' And ' to data type int.

John Cappelletti · Accepted Answer · 2019-06-18 14:41:28Z

1

When you are creating the column remainingfilter it has an implicit conversion of int

So to fix that, use ...,cast(NULL as varchar(max)) remainingfilter

That said, I would opt for concat()

Example

Select  Cmd = concat('Update ',quotename(tablename),' set ',quotename(updatecolumn),' = ',updatevalue,' And ' + nullif(remainingfilter,''))
From #Temp

Returns

Update [CRDB_Reporting] set [MagnitudePackage] = 'XLFWH'

answered Jun 18, 2019 at 14:41

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting "Conversion Failed" Error in SQl Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related