Active7 years, 11 months ago
I would always use spexecutesql these days, all it really is is a wrapper for EXEC which handles parameters & variables. However do not forget about OPTION RECOMPILE when tuning queries on very large databases, especially where you have data spanned over more than one database and are using a CONSTRAINT to limit index scans. Mar 30, 2017 How to: Directly Execute SQL Queries.; 2 minutes to read +5; In this article. LINQ to SQL translates the queries you write into parameterized SQL queries (in text form) and sends them to the SQL server for processing.
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:-
I have a query like:-
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.
narinder saggar
narinder saggarnarinder saggar
2 Answers
The below approach works fine. but be extra careful as it is susceptible to sql injection if user provides the input.
Vivek ViswanathanVivek Viswanathan
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:
gbngbn360k6262 gold badges509509 silver badges602602 bronze badges
Not the answer you're looking for? Browse other questions tagged sqlsql-serversql-server-2008 or ask your own question.
Active2 years, 6 months ago
I have an awesome query that populates my virtual table with a list of scripts that create non clustered indices for all foreign keys.
However this list is great but, I want to take it another step further and run a dynamic query to execute each script.
I don't really have that much exp with dynamic query's as most time I simply replicate functionality in my c# software instead.
So I have..
now this is not going to work as im returning mulitple rows.
What I need to do is to simply get each row and execute one after the other.
Execute Sql Query Online
For the life of me my brain has had a melt down and I just cannot see it.
I know this is a simple fix, what am I missing?
UPDATE ::
Just to add here's what my script generates in each row of my table, table has just 1 column.
This code is what is in my rows.
As a treat I thought I would post this script that generates the scripts.
OK, I followed JKN solution but the generated code when checked does not like IF
the error is 'Incorrect syntax near the keyword 'IF'.' Again I am not sure what syntax is needed for dynamic sql, is there some syntax missing? Can anyone spot this?
peterh6,9331515 gold badges5656 silver badges7575 bronze badges
lemunklemunk1,38988 gold badges4848 silver badges8181 bronze badges
1 Answer
You can use this syntax to build a string from a list of rows. Please be advised it's not officially supported and may cause issues if you start using things like
Andriy MORDER BY
:66k1414 gold badges8181 silver badges134134 bronze badges
JNKJNK52.7k1313 gold badges105105 silver badges126126 bronze badges