How do I pull a raw SQL query and swap the values in where condition

Question

1.00/5 (1 vote)

See more:

I need to pull a SQL Query which is stored as a view in SQL. Then the values in the where condition must be swapped with new values and give the result from the new query.
Example:
If the Query in SQL View is like this,
SELECT Name, Title, City, State
FROM Contacts
WHERE city = 'Buffalo'
AND Title IN ('Master', 'Trade')
OR Name != 'Chris'

I have to pull this Query and swap the values of the columns
Example:These values comes from some configuration
City = New Jersey, Name = Mark

Find if there is a column named city and replace the value of that column with New Jersey and same for the Name.
As the title is not mentioned, no need to change that one. Execute the query with new values and get the result set.

What I have tried:

I tried with ADO.Net, but there is no progress. I have no idea on how to pull a raw SQL Query. Please suggest.

Posted 20-Apr-16 5:48am

Member 12426391

Updated 21-Apr-16 7:51am

Add a Solution

Comments

CHill60 20-Apr-16 11:51am

Your explanation of what you are trying to do is not very clear. What do you mean by "pull a raw SQL Query"?
Your view (as written above) won't work by the way ... you need some brackets to clarify which bits should be AND'd and OR'd

Member 12426391 20-Apr-16 12:23pm

That's an example query, I don't know whether it works or not. I thought it would be easy to understand if I give an example.
So, imagine there are few queries saved as views in SQL and there are few column names and values coming from a configuration. I need to pull those Queries and identify the columns and replace the value of the column with the value coming from configuration and run the query with new values.

Ronnie Kong 20-Apr-16 12:36pm

Are you trying to filter the results of a query?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

phil.o · Answer 1 · 2016-04-20T06:56:00

If you want to retrieve the SQL code that created a given view, the query is:

SQL

SELECT
  m.definition
FROM
  sys.views v
  INNER JOIN sys.sql_modules m ON m.object_id = v.object_id
WHERE
  name = 'YourViewName'

Then you will have to transform it so that you can use variables. For example:
SQL query:

SQL

SELECT
  Name
 ,Title
 ,City
 ,State
FROM
  Contacts
WHERE
  City = @city
  AND Title IN ('Master', 'Trade')
  OR Name <> @name

Usage:

C#

string sqlQuery = "SELECT Name, Title, City, State FROM Contacts WHERE City = @city AND Title IN ('Master', 'Trade') OR Name <> @name";

using (SqlConnection cn = new SqlConnection(connectionString))
{
   cn.Open();

   using (SqlCommand cmd = new SqlCommand(sqlQuery, cn))
   {
      cmd.Parameters.AddWithValue("@city", "New Jersey");
      cmd.Parameters.AddWithValue("@name", "Mark");
      // ...
   }
}

The WHERE clause is ambiguous; do you mean

SQL

WHERE
  (City = @city AND Title IN ('Master', 'Trade'))
  OR Name <> @name

or

SQL

WHERE
  City = @city
  AND (Title IN ('Master', 'Trade') OR Name <> @name)

?

Hope this helps.