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 = 'w3wiki' SELECT @inp AS Result
Output:
Result |
---|
w3wiki |
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 = w3wiki SELECT @inp AS Result
Output
Msg 107, Level 6, State 1, Line 2 Invalid column name w3wiki.
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 'w3wiki'
Output
Result |
---|
w3wiki |
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 w3wiki
Output
Result |
---|
w3wiki |
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 Beginnerfor Beginner
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.
Contact Us