how to convert string into query in sql server – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server, sql-server-2008. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
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.
Solution :
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)
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)
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.