How do I declare and assign a variable on a single line in SQL

Question

I want something like

DECLARE myVariable nvarchar[MAX] = "hello world".

Bonus points if you show me how to encode a quote in the string.

E.g.:

I want the string to read

John said to Emily "Hey there Emily"

my attempt would be

DECLARE myVariable nvarchar[MAX] = "John said to Emily \"Hey there Emily\""

The string delimiter in SQL Server is `'`, not `"`. – Oded Aug 19 '10 at 19:39 — Oded, Aug 19 '10 at 19:39

score 233 · Accepted Answer · answered Aug 19 '10 at 19:40

233

Here goes:

DECLARE @var nvarchar(max) = 'Man''s best friend';

You will note that the ' is escaped by doubling it to ''.

Since the string delimiter is ' and not ", there is no need to escape ":

DECLARE @var nvarchar(max) = '"My Name is Luca" is a great song';

The second example in the MSDN page on DECLARE shows the correct syntax.

answered Aug 19 '10 at 19:40

Oded

11

You can also initialize from a select statement, eg: declare @eid uniqueidentifier = (select top 1 id from t_Event) – Damien Sawyer Sep 08 '16 at 20:32

score 18 · Answer 2 · answered Aug 19 '10 at 19:43

on sql 2008 this is valid

DECLARE @myVariable nvarchar(Max) = 'John said to Emily "Hey there Emily"'
select @myVariable

on sql server 2005, you need to do this

DECLARE @myVariable nvarchar(Max) 
select @myVariable = 'John said to Emily "Hey there Emily"'
select @myVariable

score 6 · Answer 3 · answered Aug 19 '10 at 19:41

You've nearly got it:

DECLARE @myVariable nvarchar(max) = 'hello world';

See here for the docs

For the quotes, SQL Server uses apostrophes, not quotes:

DECLARE @myVariable nvarchar(max) = 'John said to Emily "Hey there Emily"';

Use double apostrophes if you need them in a string:

DECLARE @myVariable nvarchar(max) = 'John said to Emily ''Hey there Emily''';

3 Answers3