CODEWITHSUNDEEP
sqlsql-server
hermann
hermann

Reputation: 6295

Conversion failed error with dynamic SQL query

the query below throws this error :

Conversion failed when converting the varchar value 'query below that stops at WHERE tsv.Transition_ID =' to data type int

DECLARE @Language_ID INT
SELECT @Language_ID = dbo.BPE_F_Default_Language_GetOne()

DECLARE @Transition_ID int
SET @Transition_ID = -1

DECLARE @SQLSTR nvarchar(4000)
SELECT @SQLSTR = 'SELECT tsv.Transition_Set_Variable_ID
                        , tsv.Set_To_Variable_ID
                        , tsv.Transition_ID
                        , tl.Transition_Text
                        , tsv.Variable_ID_To_Change
                        , variable.Name
                        , tsv.Set_To_Variable_ID
                        , tsv.Set_To_Value_ID
                        , tsv.Changed_In_SP
                        , tsv.Set_To_Comment_Input
                        , tsv.Comment AS Assigned_Comment
                        , variable2.Name AS Set_To_Variable_Name
                        , value.Name AS Set_To_Value_Name
                        FROM BPE_T_VA_Variable AS variable 
                        INNER JOIN BPE_T_VA_Transition_Set_Variable AS tsv 
                            ON variable.Variable_ID = tsv.Variable_ID_To_Change
                        LEFT JOIN BPE_T_VA_Variable AS variable2 
                            ON tsv.Set_To_Variable_ID = variable2.Variable_ID
                        LEFT JOIN BPE_T_VA_Value AS value 
                            ON tsv.Set_To_Value_ID = value.Value_ID
                        INNER JOIN BPE_T_WF_Transition_Localisation AS tl
                            ON tsv.Transition_ID = tl.WF_Transition_ID
                        WHERE tsv.Transition_ID = ' + @Transition_ID + ' OR ' + @Transition_ID + ' = -1 
                        AND (' + @Column + ' LIKE ''%''' + @search_string + '''%'' )
                        AND tl.Language_ID = ' + @Language_ID + '
                        ORDER BY tsv.Transition_ID, variable.Name'
EXEC(@SQLSTR);

Anyone know what this could be?

Upvotes: 1

Views: 2077

Answers (4)

Beth
Beth

Reputation: 9607

isn't this causing you problems?

WHERE tsv.Transition_ID = ' + @Transition_ID + ' OR ' + @Transition_ID + ' = -1

I would think it would have to be

WHERE tsv.Transition_ID = ' + @Transition_ID + ' OR tsv.Transition_ID =' + @Transition_ID + ' = -1

since you're setting the value to -1, I would expect the SQL, if you printed it, to look like:

WHERE tsv.Transition_ID = -1 OR -1 = -1

was that the intention?

Upvotes: 0

Gustavo F
Gustavo F

Reputation: 2206

Cast your Int variables to varchar:

SELECT @SQLSTR = 'SELECT tsv.Transition_Set_Variable_ID
                        , tsv.Set_To_Variable_ID
                        , tsv.Transition_ID
                        , tl.Transition_Text
                        , tsv.Variable_ID_To_Change
                        , variable.Name
                        , tsv.Set_To_Variable_ID
                        , tsv.Set_To_Value_ID
                        , tsv.Changed_In_SP
                        , tsv.Set_To_Comment_Input
                        , tsv.Comment AS Assigned_Comment
                        , variable2.Name AS Set_To_Variable_Name
                        , value.Name AS Set_To_Value_Name
                        FROM BPE_T_VA_Variable AS variable 
                        INNER JOIN BPE_T_VA_Transition_Set_Variable AS tsv 
                            ON variable.Variable_ID = tsv.Variable_ID_To_Change
                        LEFT JOIN BPE_T_VA_Variable AS variable2 
                            ON tsv.Set_To_Variable_ID = variable2.Variable_ID
                        LEFT JOIN BPE_T_VA_Value AS value 
                            ON tsv.Set_To_Value_ID = value.Value_ID
                        INNER JOIN BPE_T_WF_Transition_Localisation AS tl
                            ON tsv.Transition_ID = tl.WF_Transition_ID
                        WHERE tsv.Transition_ID = ' + cast(@Transition_ID as varchar) + ' OR ' + cast(@Transition_ID as varchar) + ' = -1 
                        AND (' + @Column + ' LIKE ''%''' + @search_string + '''%'' )
                        AND tl.Language_ID = ' + cast(@Language_ID as varchar) + '
                        ORDER BY tsv.Transition_ID, variable.Name'

Upvotes: 0

Taryn
Taryn

Reputation: 247630

Since you are concatenating an INT to a string you must convert it or it will throw the error.

Add the following:

cast(@Transition_ID as varchar(50))

To make your full script:

DECLARE @Language_ID INT
SELECT @Language_ID = dbo.BPE_F_Default_Language_GetOne()

DECLARE @Transition_ID int
SET @Transition_ID = -1

DECLARE @SQLSTR nvarchar(4000)
SELECT @SQLSTR = 'SELECT tsv.Transition_Set_Variable_ID
                        , tsv.Set_To_Variable_ID
                        , tsv.Transition_ID
                        , tl.Transition_Text
                        , tsv.Variable_ID_To_Change
                        , variable.Name
                        , tsv.Set_To_Variable_ID
                        , tsv.Set_To_Value_ID
                        , tsv.Changed_In_SP
                        , tsv.Set_To_Comment_Input
                        , tsv.Comment AS Assigned_Comment
                        , variable2.Name AS Set_To_Variable_Name
                        , value.Name AS Set_To_Value_Name
                        FROM BPE_T_VA_Variable AS variable 
                        INNER JOIN BPE_T_VA_Transition_Set_Variable AS tsv 
                            ON variable.Variable_ID = tsv.Variable_ID_To_Change
                        LEFT JOIN BPE_T_VA_Variable AS variable2 
                            ON tsv.Set_To_Variable_ID = variable2.Variable_ID
                        LEFT JOIN BPE_T_VA_Value AS value 
                            ON tsv.Set_To_Value_ID = value.Value_ID
                        INNER JOIN BPE_T_WF_Transition_Localisation AS tl
                            ON tsv.Transition_ID = tl.WF_Transition_ID
                        WHERE tsv.Transition_ID = ' + cast(@Transition_ID as varchar(50)) + ' OR ' + cast(@Transition_ID as varchar(50)) + ' = -1 
                        AND (' + @Column + ' LIKE ''%''' + @search_string + '''%'' )
                        AND tl.Language_ID = ' + @Language_ID + '
                        ORDER BY tsv.Transition_ID, variable.Name'
EXEC(@SQLSTR);

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

@Transition_ID is an INT, and you have to convert it to a VARCHAR before concatenating it with the rest of your string.

However, it would be a better idea to use sp_executeSql, and parameterize the @Transition_ID and @search_string parameters in your query. As it currently stands, it is likely vulnerable to a Sql Injection Attack.

Additionally, be very careful with what values you allow into @Column, preferably by using a white-list, because this can't be parameterized the way that you're using it, and it also opens you up to attack.

Upvotes: 2

Related Questions