I Was Today Years Old When…Stored Procedures – Optional String Quotes

Today’s post is a quick one and the first in a new series I am calling “I Was Today Years Old When…” where I share simple little nuggets that I stumbled across where I think “How…HOW have I never known this”

The series for me will serve as a reminder as to why I love working with technology – even if you are working with something you have worked with for many years, every day is a school day, even if the day’s lesson is a small one.

The first “I was today years old when…” is in relation to stored procedure calls in SQL Server.

Today’s example is that single quotes are actually optional when passing a string argument to a stored procedure.

Class in Session

Let’s create a simple but fairly useless stored procedure:

CREATE DATABASE StringQuoteTest;
GO
USE StringQuoteTest;
GO
CREATE OR ALTER PROCEDURE dbo.spGreeting
(
	@YourName NVARCHAR(50),
	@YourLocation NVARCHAR(50)
)
AS
	PRINT 'Hello ' + @YourName + ', how is the weather in ' + @YourLocation + '?';

Now for a pop quiz – which of the following calls will work?

EXEC dbo.spGreeting @YourName = John, @YourLocation = UK;
EXEC dbo.spGreeting @YourName = John, @YourLocation = 'UK';
EXEC dbo.spGreeting @YourName = 'John', @YourLocation = UK;
EXEC dbo.spGreeting @YourName = 'John', @YourLocation = 'UK';
EXEC dbo.spGreeting John, UK;
EXEC dbo.spGreeting John, 'UK';
EXEC dbo.spGreeting 'John', UK;
EXEC dbo.spGreeting 'John', 'UK';

The answer is “all of them”, the output is below:

Hello John, how is the weather in UK?
Hello John, how is the weather in UK?
Hello John, how is the weather in UK?
Hello John, how is the weather in UK?
Hello John, how is the weather in UK?
Hello John, how is the weather in UK?
Hello John, how is the weather in UK?
Hello John, how is the weather in UK?

This came as a surprise to me as some of the calls do not wrap the string argument in single quotes.

There is one caveat though, let’s change our call:

EXEC dbo.spGreeting @YourName = John Smith, @YourLocation = UK;

This time we get an error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Smith'.

This makes sense as Smith is being parsed as a new token. To specify a string parameter without quotes, the string needs to be one continuous string with no spaces. However we can’t pass an unquoted string which contains characters that are not legal in an unquoted identifier (characters that force us to wrap object names in square brackets):

EXEC dbo.spGreeting @YourName = Smith&John, @YourLocation = UK;

The above gives us a similar error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '&amp;'.

Whereas, of course, the following calls with single quotes work:

EXEC dbo.spGreeting @YourName = 'John Smith', @YourLocation = 'UK';
EXEC dbo.spGreeting @YourName = 'Smith&John', @YourLocation = 'UK';

The output is below:

Hello John Smith, how is the weather in UK?
Hello Smith&amp;John, how is the weather in UK?

The behaviour is documented in the EXEC documentation, which confirms:

If you pass a single word that doesn’t begin with @, that isn’t enclosed in quotation marks (for example, if you forget @ on a parameter name), the word is treated as an nvarchar string, in spite of the missing quotation marks.

With all that said, I don’t think passing arguments without qualifiers is particularly useful and personally think it makes code less readable, however it is something that is perhaps useful to know.

References / Further Reading

Microsoft – EXECUTE (Transact-SQL)

Stack Overflow – SQL Server parameters without “@” / string literals

Stack Overflow – SQL Server EXEC with unquoted string parameter is valid?