I have a following table with the data
--------------------
Name | Status
--------|-----------
AAA | Active
BBB | Active
CCC | Active
I am trying to get the select query work by using local variable but the query is not working and I am not getting any result.
declare
@v1 nvarchar(100)=null,
@v2 nvarchar(100)=null
set @v1='AAA'
set @v2 =' and Name=' + '''' + @v1 + ''''
SELECT *
FROM Employe
WHERE status = 'Active' + @v2
You don't need @v2
declare
@v1 nvarchar(100)=null
set @v1='AAA'
SELECT * FROM Employe WHERE status = 'Active' and Name = @v1
Since you will be creating the Where clause dynamically from the variable the Query needs to be prepared as a string and then executed by using exec command.
That is because you will need to prepare a dynamic SQL Query
declare
@v1 nvarchar(100)=null,
@v2 nvarchar(100)=null,
@SQL nvarchar(4000) = null
set @v1='AAA'
set @v2 =' and Name=' + '''' + @v1 + ''''
SET @SQL = 'SELECT * FROM Employe WHERE status = ''Active''' + @v2
EXEC @SQL
An alternative to the dynamic SQL query is conditional usage of the variable in the where clause, like below
declare @v1 nvarchar(100)=null,
set @v1='AAA'
SELECT * FROM Employe WHERE status = 'Active' AND (@v1 IS NULL OR Name = @v1)
The code above will return all records regardless of the name if the variable is NULL. When the variable is not NULL it will filter the query for the variable value.
,