CODEWITHSUNDEEP
sqlsql-serversql-server-2008
narinder saggar
narinder saggar

Reputation: 131

how to convert string into query in sql server

How to add string in which we have And clause. but when we apply that string which query this string will be treated as Query and fulfill all and conditions
I have a query like:- 

Declare @WhereQuery varchar(max)

SET @WhereQuery='class=''BCA'' and RollNo=10 AND ID IN (SELECT ID FROM StudentMaster WHERE MARKS > 50)'

SELECT  * into #TempTable1
from StudentMaster 
where @WhereQuery

I also don't want to use execute or exec function to run this query. I am going to add string with query mention as above but this will not work properly. The variable which I have added after where clause is treated as string but I want this string is treated as Query. Please help. I also don't want to use execute or exec function to run this query.

Upvotes: 1

Views: 20590

Answers (2)

gbn
gbn

Reputation: 432271

You have to use EXEC or sp_exeutesql if you want to run dynamic SQL.

If you don't want to use EXEC then write non-dynamic queries:

SELECT  * into #TempTable1
from StudentMaster 
where class='BCA' and RollNo=10 AND ID IN (SELECT ID FROM StudentMaster WHERE MARKS > 50)

Upvotes: 0

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

The below approach works fine. but be extra careful as it is susceptible to sql injection if user provides the input.

create table #TempTable1 (.....)

Declare @selectQuery varchar(max)
set @selectQuery = 'SELECT * into #TempTable1 from StudentMaster '

Declare @WhereQuery varchar(max)

SET @WhereQuery='where class=''BCA'' and RollNo=10 AND ID IN (SELECT ID FROM StudentMaster WHERE MARKS > 50)'

exec (@selectQuery + @WhereQuery)

Upvotes: 3

Related Questions