在sql SP中创建动态 [英] create Dynamic where in sql SP

问题描述

我正在尝试创建一个存储的一些值，其中一个值是列名和值。

我尝试了以下代码

I am trying to create a stored that will some values, one of the value is column name and a value.
I've tried the following code

create PROC SelectDynamic
@DateFrom DATETIME,
@DateTo DATETIME,
@ColumName NVARCHAR(50),
@ColumID INT
AS
DECLARE @Sql NVARCHAR(MAX)
SET @Sql=
'
SELECT
    *
FROM
    Ticket t 


WHERE t.TicketDate BETWEEN '+ @DateFrom +' AND' + @DateTo+' AND' + @ColumName +'='+ @ColumID

EXEC sp_executesql @Sql

它给我这个错误转换日期和/或时间从字符串转换失败。

我不是一个SQL专家，这是我第一个动态的sql语句

可以任何一个帮助

it give me this error Conversion failed when converting date and/or time from character string.
I am not a SQL expert, and it's me first dynamic sql statement
can any one help

谢谢！

推荐答案

在构建动态查询时，您必须转义引号。

You have to escape the quotation marks when building a dynamic query.

因此，您的@SQL变量应该是这个

Thus, your @SQL variable should be something like this

SET @Sql= 'SELECT * FROM Ticket t WHERE t.TicketDate BETWEEN ''' + CAST(@DateFrom AS NVARCHAR) + ''' AND ''' + CAST(@DateTo AS NVARCHAR) + ''' AND ' + @ColumName +  '=' + CAST(@ColumID AS NVARCHAR) + ''

通过将引号加倍来完成转义。

Escaping is done by doubling the quotation marks.

您可以执行 SELECT @SQL ，以测试您的查询是否已正确构建。

You can do a SELECT @SQL after to test if your query has been built correctly.

这篇关于在sql SP中创建动态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！