如何提取原始SQL查询并在where条件中交换值 [英] How do I pull a raw SQL query and swap the values in where condition

问题描述

我需要提取一个SQL查询，它在SQL中存储为视图。那么where条件中的值必须与新值交换并给出新查询的结果。

示例：

如果SQL视图中的查询是这样的，

SELECT名称，标题，城市，州

来自联系人

WHERE city ='Buffalo'

AND Title IN（'Master'，'Trade'）

OR Name！='Chris'



我必须拉这个查询并交换列的值

示例：这些值来自某些配置

City = New Jersey，Name = Mark



查找是否存在名为city的列，并将该列的值替换为New Jersey，并将其替换为Name。

由于未提及标题，因此无需更改该标题。用新值执行查询并获得结果集。



我尝试过：



我尝试使用ADO.Net，但没有进展。我不知道如何提取原始SQL查询。请建议。

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.

推荐答案

如果要检索创建给定视图的SQL代码，查询为：

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

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

然后你必须转换它以便你可以使用变量。例如：

SQL查询：

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

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

用法：

Usage:

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");
      // ...
   }
}

WHERE子句不明确;你的意思是

The WHERE clause is ambiguous; do you mean

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

或

or

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

？



希望这有帮助。

?

Hope this helps.

这篇关于如何提取原始SQL查询并在where条件中交换值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！