CODEWITHSUNDEEP
sqlsql-server
blue piranha
blue piranha

Reputation: 3874

Building insert statement with quotes in SQL

I am trying to understand how the single quotes work in SQL. All I want to achieve is

INSERT INTO LOGTABLE 
            (ID, 
             ROLLNO) 
VALUES     ('E8645F55-A18C-43EA-9D68-1F9068F8A9FB', 
            28)

Here ID is a uniqueidentifier field and rollNo is an int.

So I have this sample test code:

 set @query = '

insert into fileLog
(
    id,
    rollNo
)
values
('+
'''' + NEWID() + '''' + ',' + 28 + 
')'

print @query

I have tried several combination of single quotes left and right but nothing works. I would really appreciate if someone could solve this. But in particular I wanted to know how many single quotes are required on both sides of a string to get something like 'SQL'.

Thanks

Upvotes: 1

Views: 1574

Answers (4)

Bernhard Barker
Bernhard Barker

Reputation: 55609

Unless you need dynamic SQL for some reason, you can probably just do this:

insert into fileLog
(
  id,
  rollNo
)
values
(
  NEWID(),
  28
)

Upvotes: 2

anon
anon

Reputation:

(I'm going to assume you need dynamic SQL for reasons not obvious in the question, since this doesn't seem to require dynamic SQL at all.)

As @Gidil suggested, the problem here is trying to treat a uniqueidentifier as a string. In this case, there really isn't any reason to declare NEWID() in the outer scope, since you can simply say:

SET @query = 'INSERT ... VALUES(NEWID(), 28);';

PRINT @query;

Now, you should be using NVARCHAR(MAX) as your parameter, because ultimately you should be executing this using sp_executesql, not EXEC().

If you need to have a literal you can double up the quotes:

DECLARE @string VARCHAR(32);
SET @string = 'foo';

SET @query = N'INSERT ... VALUES(''' + @string + ''', 28);';

However I find it more readable to use CHAR(39):

SET @query = N'INSERT ... VALUES(' + CHAR(39) + @string + CHAR(39) + ', 28);';

And even better is to not append these variables to a string anyway. You should be using properly typed parameters where possible.

DECLARE @query NVARCHAR(MAX);
DECLARE @string VARCHAR(32), @newid UNIQUEIDENTIFIER, @id INT;
SELECT @string = 'foo', @newid = NEWID(), @id = 28;

SET @query = N'INSERT ... VALUES(@string, @newid, @id);';

EXEC sp_executesql @query,
  N'@string VARCHAR(32), @newid UNIQUEIDENTIFIER, @id INT',
  @string, @newid, @id;

It's bulkier, sure, but it's much safer from SQL injection and it lets you stop trying to figure out and deal with the hassle of embedding single quotes into the string...

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

My question is: Why are you using dynamic SQL? It's one of those techniques that is useful in some situations, but can be abused easily.

As for the answer to your question, I use a technique to help minimize the flipping in and out of SQL construction:

DECLARE @query VARCHAR(MAX) 

SET @query = '

insert into fileLog
(
    id,
    rollNo
)
values
(''|NEWID|'', |INT|)'

SET @query = REPLACE(@query, '|NEWID|', NEWID())
SET @query = REPLACE(@query, '|INT|', 28)


PRINT @query

Upvotes: 3

Gidil
Gidil

Reputation: 4137

Try this:

DECLARE @query VARCHAR(MAX) 

SET @query = ' insert into fileLog (     id,     rollNo ) values (' + '''' + Cast(Newid() AS VARCHAR(100)) + '''' 
             + ',28)' 

PRINT @query

The problem isn't the quotes, but the data types.
NEWID isn't a string and neither is the number 28.

Good luck!

Upvotes: 2

Related Questions