Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi All,

Below query is not returning any row but DB has the value. Can anyone help on this without using dynamic query
DECLARE @Search1 NVARCHAR(200)                                  
 DECLARE @Search2 NVARCHAR(200)
 SET @Search1 = '1673437'
 SET @Search2 = 'SSN'
 SELECT TOP 10 ID, FirstName, LastName, Age FROM Persons WHERE  @Search2 = @Search1



with regards,

stellus.


What I have tried:

Column name variable without dynamic query 
Posted
Updated 23-Nov-17 8:16am
Comments
Santosh kumar Pithani 23-Nov-17 2:30am    
In your query filter(where 1=2) condition fails so its returns only headers.
Dave Kreskowiak 23-Nov-17 14:44pm    
You can't do it without a dynamically built query. Well, you could, but as Richard pointed out, the code is really ugly and doesn't make anything easier.

That's precisely the sort of thing dynamic queries are meant for.
SQL
DECLARE @Search1 NVARCHAR(200);
DECLARE @Search2 NVARCHAR(200);

SET @Search1 = '1673437';
SET @Search2 = 'SSN';


-- Never trust user input!
DECLARE @SearchColumn sysname;

SELECT 
    @SearchColumn = QUOTENAME(name)
FROM
    sys.columns
WHERE
    name = @Search2
And
    object_id = OBJECT_ID(N'Persons')
;

DECLARE @command nvarchar(max);
SET @command = N'SELECT TOP 10 ID, FirstName, LastName, Age FROM Persons WHERE ' + @SearchColumn + N' = @Search1';
EXEC sp_executesql @command, N'@Search1 nvarchar(200)', @Search1 = @Search1;

sp_executesql (Transact-SQL) | Microsoft Docs[^]

If for some bizarre reason you can't, or don't want to, use a dynamic query, then your only option is a separate check for every possible column:
SQL
SELECT TOP 10 
    ID, 
    FirstName, 
    LastName, 
    Age 
FROM 
    Persons 
WHERE 
    (@Search2 = N'SSN' And SSN = @Search1)
Or 
    (@Search2 = N'FirstName' And FirstName = @Search1)
Or 
    ...
 
Share this answer
 
SELECT TOP 10 ID, FirstName, LastName, Age,@Search1  AS Col1,@Search2 AS Col2
 FROM Persons
   WHERE  @Search1 = @Search1 And @Search2 = @Search2  
 
Share this answer
 
Comments
stellus 23-Nov-17 2:49am    
hi santosh,
thanks for reply,
i want to call @search1 directly in where condition,
like this,
SELECT TOP 10 ID, FirstName, LastName, Age FROM Persons WHERE @Search2 = @Search1
is this possible?
Santosh kumar Pithani 23-Nov-17 3:32am    
its is possible but @Search2,@Search1 both values should be same.
Dave Kreskowiak 23-Nov-17 14:42pm    
Wrong, wrong, wrong. Your query, if it to work at all, would only return ALL the rows in the table, not filtered at all.
Santosh kumar Pithani 26-Nov-17 22:45pm    
My query is correct according OP "WHERE @Search2 = @Search1" this condition is works as true or false ,it return all rows if it is true(1=1) else false(1=3)zero rows.I don't know why he has posted like his question without dynamic query:)
Dave Kreskowiak 26-Nov-17 23:47pm    
That's not what you put in your "solution":
WHERE @Search1 = @Search1 And @Search2 = @Search2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900