how to convert string into query in sql server

Question

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.

-1 for choosing dynamic SQL but then saying "not EXEC".

gbn
– gbn

2011-10-25 05:59:27 +00:00
Commented Oct 25, 2011 at 5:59 — gbn
– gbn, Commented Oct 25, 2011 at 5:59

Vivek Viswanathan · Accepted Answer · 2011-10-25 05:27:02Z

3

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)

answered Oct 25, 2011 at 5:27

Vivek Viswanathan

1,96318 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

narinder saggar Over a year ago

I don't want to use execute or exec function to run this query.

Vivek Viswanathan Over a year ago

In that case, construct the query in Java/.Net and then prepare it and use it.

gbn · Accepted Answer · 2011-10-25 06:00:33Z

0

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)

answered Oct 25, 2011 at 6:00

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

how to convert string into query in sql server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related