需要DYNAMIC在哪里加入SQL中的条件 [英] NEED DYNAMIC WHERE CONDTION IN JOIN SQL

问题描述

ALTER PROC [SpData].[GETALLTEMP]
@DYNAMICWHERE NVARCHAR(MAX)
AS
(SELECT T2.METRICS, T1.PERIOD, SUM(T2.AMOUNT) AS AMOUNT  FROM Masters.PERIODS T1
LEFT OUTER JOIN Masters.TransESOP T2
ON
T1.PERIOD = T2.PERIOD
AND
UPPER(T2.METRICS) LIKE '%CELCIUS%' AND @DYNAMICWHERE
GROUP BY T1.PERIOD, T2.METRICS)
ORDER BY T1.PERIOD ASC

需要传递如下语法

EXEC GETALLTEMP'在哪里[COLUMN] = XXXX和COLUMN = YYYY .......'



返回错误：

Need to pass like below syntax
EXEC GETALLTEMP 'WHERE [COLUMN] = XXXX AND COLUMN =YYYY.......'

returns Error:

An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'.

i需要根据用户选择从前端传递一些动态条件

请帮我解决这个问题我是sql server的新手

i need to pass some dynamic condition from Front end based on user selection
Kindly help me to solve this i am new to sql server

推荐答案

您可以将整个语句用作动态。如下所示

You can do it by using entire statement as dynamic.As below

ALTER PROC [SpData].[GETALLTEMP]
@DYNAMICWHERE NVARCHAR(MAX)
AS
DECLARE @cmd AS VARCHAR(MAX)

SET @cmd = '(SELECT T2.METRICS, T1.PERIOD, SUM(T2.AMOUNT) AS AMOUNT  FROM Masters.PERIODS T1
LEFT OUTER JOIN Masters.TransESOP T2
ON
T1.PERIOD = T2.PERIOD
AND
UPPER(T2.METRICS) LIKE ''%CELCIUS%'' AND + ' @DYNAMICWHERE +'
GROUP BY T1.PERIOD, T2.METRICS)
ORDER BY T1.PERIOD ASC'
EXEC(@cmd)

当您需要动态更改SQL时，需要将SQL构建为字符串（即你在你的例子中想要做的事情，遗憾的是，它不会工作）：

When you need to dynamically alter the SQL, you need to build the SQL as a string (i.e. what you are trying to do in your example simply will not, sadly, work):

DECLARE @Sql NVARCHAR(someBigEnoughValue)

SET @Sql = 'fixed SQL' + [[variable SQL]] + 'more fixed SQL if needed'

EXEC sp_executesql @Sql

而不是将整个 WHERE 子句传递到存储过程中，它可能更明智（读取 - 更安全的SQL注入攻击）来传递参数，这可能是NULL ，并在存储过程中构建整个SQL。



这也使得从数据库管理工具中调试sproc变得更容易（你不需要应用程序来提供 WHERE clause）;在T-SQL（也可能是其他方言）中，您可以使用 PRINT 语句（可能，如果@Debug参数设置为1）来查看生成的动态SQL。 ..然后，您可以将其复制/粘贴到另一个查询窗口中以运行并验证其结果，查看它产生错误的位置等，而无需运行存储过程（这使得精确定位错误）。

Rather than passing a whole WHERE clause into your stored procedure, it might be wiser (read - safer from possible SQL injection attacks) to pass parameters, which may be NULL, and build the entire SQL in the stored procedure.

That also makes it easier to debug the sproc from within a database management tool (you don't need the app to supply the WHERE clause); in T-SQL (probably other dialects, too) you can use the PRINT statement (perhaps, if an @Debug parameter is set to 1) to view the generated dynamic SQL ... which you can then copy/paste into another query window to run and verify its results, see where it's generating errors, etc., without having to run a stored procedure (which makes pinpointing errors hard).

您需要传递参数而不是整个WHERE语句：

You need to pass parameters rather than the whole WHERE statement:

ALTER PROC [SpData].[GETALLTEMP]
@SOME_VALUE VARCHAR(30)
AS
(SELECT T2.METRICS, T1.PERIOD, SUM(T2.AMOUNT) AS AMOUNT  FROM Masters.PERIODS T1
LEFT OUTER JOIN Masters.TransESOP T2
ON
T1.PERIOD = T2.PERIOD
AND
UPPER(T2.METRICS) LIKE '%CELCIUS%' 
AND SOME_COLUMN_NAME=@SOME_VALUE
GROUP BY T1.PERIOD, T2.METRICS)
ORDER BY T1.PERIOD ASC

这篇关于需要DYNAMIC在哪里加入SQL中的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！