Open In App

Use of Single Quotes for Stored Procedure Parameters in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

Whenever any value is passed to the variable or column of character data type, the string value has single quotes(”) around them otherwise it will through an error. Below we will discuss this feature (Use of Single Quotes for Stored Procedure Parameters in SQL Server) of SQL Server.

Example 1:

DECLARE @inp VARCHAR(100)
SET @inp = 'GeeksforGeeks'
SELECT @inp AS Result

Output:

Result
GeeksforGeeks

Example 2:

DECLARE @var VARCHAR(100)
SET @var = 'LearningSQL'
SELECT @var AS Result

Output:

Result
LearningSQL

Now let us run the following query without the single quote around the variable.

Example 1:

DECLARE @inp VARCHAR(100)
SET @inp = GeeksforGeeks
SELECT @inp AS Result

Output

Msg 107, Level 6, State 1, Line 2
Invalid column name GeeksforGeeks.

Example 2:

DECLARE @var VARCHAR(100)
SET @var = LearningSQL
SELECT @var AS Result

Output

Msg 207, Level 7, State 1, Line 2
Invalid column name LearningSQL.

From the above example, it is observed that we have to use a single quote around the variable.

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

1. Let us create a stored procedure named ‘GeekTest’.

CREATE PROCEDURE GeekTest (@Inp VARCHAR(100))

AS

SELECT @Inp AS Result

GO

Now let us call the stored procedure with a parameter with single quotes.

Example

EXEC GeekTest 'GeeksforGeeks'

Output

Result
GeeksforGeeks

We could see it will give the same output as an output before.

2. Let us create a stored procedure named ‘ProcTest’.

CREATE PROCEDURE ProcTest (@Var VARCHAR(100))

AS

SELECT @Var AS Result

GO

Now let us call the stored procedure with a parameter with single quotes.

Example

EXEC ProcTest 'LearningSQL'

Output

Result
LearningSQL

We could see it will give the same output as an output before.

Now, let us verify the SQL feature and run the same stored procedure without the single quotes.

Example

EXEC GeekTest GeeksforGeeks

Output

Result
GeeksforGeeks

Example

EXEC ProcTest LearningSQL

Output

Result
LearningSQL

Conclusion :

When passing values to Stored Procedure arguments which are of character datatypes, the single quotes are optional when the string value does not contain any space. 

Note: If there is any space in the string value, it will throw an error of the invalid column name.

Example 1:

EXEC GeekTest Geeksfor Geeks

Output

Msg 107, Level 8, State 2, Line 1
Invalid column name.

Example 2:

EXEC ProcTest Learning SQL

Output

Msg 207, Level 9, State 2, Line 1
Invalid column name.

Last Updated : 17 Feb, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads