1

I am trying to write a simple SP by building dynamic queries and storing in a variable and executing the variable.

I currently get the following error:

Msg 206, Level 16, State 2, Line 16
Operand type clash: datetime2 is incompatible with float

For the following code: 

DECLARE 
@table_Num
@1 varchar(100) = 'boo',
@2 int =2,
@3 varchar(100) ='default',
@4 varchar(50) = NULL,
@5 int =NULL,
@6 float =12,
@7 datetime2(0) ='1970-01-01 00:00:00',
@8 datetime2(0)='1970-01-01 00:00:00',
@9 varchar(50)='',
@10 varchar(50)=NULL,
@11 decimal(18,0)=0000000000000,
@12 int =999999

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = 'INSERT INTO abc_'+@table_Num+'(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12) 
VALUES ('+@1+',2,'+@3+','+@4+','+@5+','+@6+','+@7+','+@8+','+@9+','+@10+','+@11+','+@12+')'

EXEC (@SQLString)

As far as I can see the variables are same type as table col types. Any ideas?

Improve this question
7
  • Can you include the definition of table_1 as well please? Commented Apr 15, 2013 at 14:29
  • 2
    This is the absolute worst possible way to build a dynamic SQL string. Can you explain why it needs to be dynamic SQL in the first place? Commented Apr 15, 2013 at 14:31
  • @AaronBertrand great point. I just took it for granted that there was even a reason for this to be dynamic SQL. Commented Apr 15, 2013 at 14:33
  • I thought it had to be dynamic sql as i neeed the params to be in the insert statment Commented Apr 15, 2013 at 15:03
  • @Fearghal you can call the params/variables directly in your insert statement. No need to use dynamic SQL unless you are building a different insert statement each time (like leaving out certain params, etc.) Commented Apr 15, 2013 at 15:20

3 Answers 3

Reset to default
3

To create a correct insert statement dynamically you would have to, at the very least:

  1. Cast variables to strings: cast(@2 as varchar(11))
  2. Double apostrophes in strings: replace(@1, '''', '''''')
  3. Wrap strings (including dates as strings) in apostrophes: '''' + replace(@1, '''', '''''') + ''''
  4. Handle nulls, since concatenating a null yields null: coalesce(cast(@5 as varchar(11)), 'null')

Doesn't this look terrible?

As mentioned by others, this is the worst way to run dynamic SQL, and opens a major security hole called SQL Injection. If this needs to be dynamic, please use sp_executesql with typed parameters instead (Thanks, @JeffRosenberg). However, this simple insert is also a trivial example and shouldn't require dynamic SQL at all (Thanks, @AaronBertrand). Simply run the command as-is:

INSERT INTO table_1 (col1, col2, /* ... */ col12)
VALUES (@1, @2, /* ... */ @12)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi guys, sorry i meade a mistake in my example, it prob doesnt make too much diff but heres the diff.... I want to be able to state the tabel name being inserted into dymamically, I have changed the original questions code accordingly
3

I'm guessing it has something to do with your use of quotation marks, but it's quite hard to follow. I'd recommend using EXEC sp_executesql and passing your parameters as actual parameters. It's a little easier to do for a statement like this, and much more secure.

(Edited based on comment from TimLehner)

DECLARE @SQLString NVARCHAR(MAX), @ParamString NVARCHAR(MAX), @TableName sysname;
SET @TableName = N'abc_' + REPLACE(@table_num, '''', ''''''); --Escape apostrophes

IF OBJECT_ID(@TableName) IS NOT NULL
BEGIN
  SET @SQLString = 
    N'INSERT INTO ' + QUOTENAME(@TableName) +
    N' (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12)
       VALUES (@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12)';
  SET @ParamString = N'@1 varchar(100), @2 int, @3 varchar(100), ... , @12 int)';

  EXEC sp_executesql @SQLString, @ParamString, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12
END

Read about sp_execultesql on MSDN here.

Improve this answer

8 Comments

+1 sp_executesql is absolutely the way to execute dynamic SQL, particularly when most of the parameters can be strongly typed (vs. glomming them together as one big string).
@AaronBertrand thanks for the edit. You're right, the parens were a mistake.
guys, i amended my original quiesion - i wanted to state the table name dynamically and this wasnt in example doh! Does this change anything - eg '....table_'+@table_num+'(....
That will still work very well with sp_executesql. I've updated my answer above; it's only a small change. You'll need to concatenate the table name into the SQL command, but everything else should still be passed as parameters.
Thanks agaan. I dupliacted this, added a few tweaks to make it work -' at end of last line, declared table_num....getting error: Must declare the scalar variable @1.
|
0

You have a casting problem in your string concatenation. You need to cast your parameters to varchar to build your string.

cast(@7 as varchar(50)).

FWIW, I think that you should rethink how you are building up your query. If possible, avoid using EXEC(). You will run in to all kinds of problems with this solution. You might also get a problem SQL injection. Your insert query could be rewritten if a hacker manages to send int SQL code in e.g. the parameter @1

Improve this answer

1 Comment

Thanks, i tried the varchar approach, think i ran into another issue but il check it out again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.