SQL SERVER – Stored Procedure Parameters and Single Quotes

Recently during Comprehensive Database Performance Health Check I had an interesting conversation with Sr. DBA about Stored Procedure Parameters and Single Quotes. Let us learn about that in today’s blog post.

When we pass values to the variable or column of character data type, the string values should have single quotes around them otherwise we will get an error. Let us see a simple example.

DECLARE @var VARCHAR(100)
SET @var = 'TestString'
SELECT @var AS ResultString

The script above will work just fine and return the results as follows:

Now let us run the following script where we do not provide the single quote around the variable assignment.

DECLARE @var VARCHAR(100)
SET @var = TestString
SELECT @var AS ResultString

When we run the script above we get the following error:

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘TestString’.

It is very clear from this example that we need to have a single quote around the variable assignment.

Now let us run the same example with the stored procedure.

First, let us create a stored procedure.

CREATE PROCEDURE ParamTesting (@Param VARCHAR(100))
AS
SELECT @Param AS ResultString
GO

Now let us run the stored procedure with a parameter and we will make sure that the parameter is wrapped with single quotes.

EXEC ParamTesting 'TestString'

When you run the SP as mentioned above it will give you similar results as we have got successful results before.

Experiment with Procedure Parameters

Now is the time when we do a little experiment. Let us run the same stored procedure without the single quotes around the parameter and find out what happens.

EXEC ParamTesting TestString

Now when I ran the above-stored procedure, it is quite possible that we are all thinking that we will have an error like an Invalid column name. However, in the reality, it will give us results just like before successfully.

If you do not believe me, then you should try this one out yourself. 

When passing values to Stored Procedure parameters which are of character datatypes, the single quotes are optional provided that the string value does not contain any space. If you have any space in your string value, you will encounter an error of an invalid column name.

Well, that’s it for today. Please leave a comment if you did not know about this tiny feature of SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)