Use of Single Quotes for Stored Procedure Parameters in SQL Server
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:
Example 2:
DECLARE @var VARCHAR(100)
SET @var = 'LearningSQL'
SELECT @var AS Result
Output:
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
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
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
Example
EXEC ProcTest LearningSQL
Output
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
Share your thoughts in the comments
Please Login to comment...