Executing dynamic SQL using EXEC - Error

Transact-SQL
1

I have an issue with the following:
-- the following is working
INSERT INTO Z_ToolLog
(Tool, Unit, Run, ProcessSendDate, ProcessTime, Recipe, Action, Remark, UserName, L1, L2,
L3, L4, LogDate, EntryDate)
VALUES
(@t14, @t8, @t7, @t9, @t10, @t11, @t12, @t13, @t6, @T0,@t1, @t2,@t3,@t5,@t15)

**the following is NOT working with error: **
conversion Failed when converting nvarchar value 'INSERT INTO Z_ToolLog (Tool, Unit, Run, ProcessSendDate, ProcessTime, Recipe, Action, Remark, UserName, L1, L2, L3, L4, LogDate, EntryDate) VALUES ('CVD',' to data type int

SET @SQL = CASE
WHEN @Exe = 2
THEN 'Print 1'
ELSE '
INSERT INTO Z_ToolLog
(Tool, Unit, Run, ProcessSendDate, ProcessTime, Recipe, Action, Remark, UserName, L1,
L2, L3, L4, LogDate, EntryDate)
VALUES
(''' + @t14 + ''',
' + @t8 +',
' + @t7+',
' + @t9+',
'+ @t10+',
'''+ @t11+''',
'''+ @t12+''',
'''+ @t13+''',
'''+ @t6+''',
'''+ @T0+''',
'''+@t1+''',
'''+ @t2+''',
'''+@t3+''',
' +@t5+',
' +@t15+')'
END;
EXEC @SQL;

not sure why the error, double-check the definitions of the parameters and the table setting
Does anyone have an idea?
Thanks, Avner

2

Maybe those double quotes are the issue

3

Hi
Thank you, but no as the data in the parameter is text and need the qoutes

4

The code fragment you provided is not complete - i.e., the variables have not been defined. So I am unable to test it. However, the way to debug this would be to first replace the statement

EXEC @SQL

with

SELECT @SQL;

Then run the query, which will print out the SQL statement. Copy that statement and try to execute it and you should get the same error. Now examine the statement to see which column is causing the error.

2 Likes
5

ah got it. as @JamesK has mentioned you are going to have to provide us the whole code

meaning

create table Z_ToolLog(all of the fields)

and the data types for all of the variables also

@t9, @t10, @t11, @t12, @t13, @t6, 
@T0,@t1, @t2,@t3,@t5,@t1 etc
``
6

The error states what the issue is:

VALUES ('CVD',' to data type int

It is trying to convert CVD to an integer. The values aren't what you think they are. Better to use what JamesK said and select the @sql parameter to see what is actually being created.

7 
SET @SQL = CASE
WHEN @Exe = 2
THEN 'Print 1'
ELSE '
INSERT INTO Z_ToolLog
(Tool, Unit, Run, ProcessSendDate, ProcessTime, Recipe, Action, Remark, UserName, L1,
L2, L3, L4, LogDate, EntryDate)
VALUES
(''' + @t14 + ''',
' + CAST(@t8 AS varchar(8000)) +',
' + CAST(@t7 AS varchar(8000)) +',
' + CAST(@t9 AS varchar(8000)) +',
'+ CAST(@t10 AS varchar(8000)) +',
'''+ @t11+''',
'''+ @t12+''',
'''+ @t13+''',
'''+ @t6+''',
'''+ @T0+''',
'''+@t1+''',
'''+ @t2+''',
'''+@t3+''',
' +CAST(@t5 AS varchar(8000)) +',
' +CAST(@t15 AS varchar(8000)) +')'
END;
PRINT @SQL;
EXEC @SQL;
1 Like
8

A much better option would be to use sp_executesql instead of concatenating a string. Using that you would create your insert statement with parameters and declare/pass those parameters to the embedded code.

But further to that - I see no reason to use dynamic SQL here. You can just pass the variables to the insert statement directly - unless something in those variables needs to be executed.

1 Like
9

Have to agree @jeffw8713. Why make this dynamic? If those variables are coming in as parameters you can leverage send them as xml, json, or better yet Table Types

Create type foo as table(
Appid int,
Grill bit,
Lamb int
)
10

HI
than you for the answer,
we used dynamic as we need the insert to run only if the user changes one of the parameters,
in any case, putting the none text parameter values in this way ''' + CAST(@t8 AS NVARCHAR(50)) + ''', it worked.

11

thats works, thanks