Inserting Values into Dynamic Sql within a stored procedure - Where do the ticks go?

Question

I'm trying to create sql statement in TSQL that looks like this:

INSERT INTO UsersTable (UserName) VALUES (@UserName)

This is easy until you're trying to do it dynamically in T-Sql and @UserName is a varchar

The it looks like this:

SELECT @SQLInsert = 'INSERT INTO UsersTable (UserName) ' +
                    'VALUES (' + @UserName + ')'

Except of course this doesn't work. How many ticks do I need to create ticks in a string???? Driving me crazy!

Answer 1

Literal single-quotes within strings are represented by two consecutive single-quotes.

See Constants (Transact-SQL).

Answer 2

I think the problem is your not quoting the @UserName string properly. The best practices way (and safe way) to do this is to use a parameterized query using sp_executesql. Below is how it would be done using sp_executesql (untested). I hope this helps.

DECLARE @SQLInsert NVARCHAR(500)
DECLARE @paramDef NVARCHAR(500) 

SET @SQLInsert = 'INSERT INTO UsersTable (UserName)  VALUES ( @InsUserName )'

SET @paramDef = '@InsUserName NVARCHAR(50)'

EXECUTE sp_executesql @SQLInsert, @paramDef,
                      @InsUserName = @UserName;

Answer 3

I'm curious as to why you are placing the ' + ' between the first part and the VALUES. It is unnecessary, but other than that go with Justin's answer.

Answer 4

What you are trying to do is vulnerable to SQL Injections:

http://en.wikipedia.org/wiki/SQL_injection

It is very unsafe to wrap your value in quotes. It is safer to use sp_executesql with parameters or not use it at all: http://msdn.microsoft.com/en-us/library/ms188001.aspx