SQSERVER 2008 EXP中的动态WHERE子句. [英] Dynamic WHERE CLAUSE IN SQSERVER 2008 EXP.

问题描述

我正在尝试在我的存储过程中生成动态的where子句.

=== @population nvarchar(max)

declare    @chvSelect varchar(max),
             @chvFrom varchar(max),
             @chvWhere varchar(max),
             @chvOrderby varchar(max),
             @chvSQL varchar(max)
BEGIN
 

--if @Population is not null

set @chvSelect=''SELECT Localities.Name,FormFields.FieldName, FormInstanceData.Value''  
set @chvFrom='' FROM   FormInstances INNER JOIN
                      FormInstanceData ON FormInstances.Id = FormInstanceData.FormInstanceId INNER JOIN
                      Communities INNER JOIN
                      Localities ON Communities.Id = Localities.Id INNER JOIN
                      WCData ON Localities.Id = WCData.LocalityId ON FormInstances.Id = WCData.FormInstanceId INNER JOIN
                      FormFields ON FormInstanceData.FieldId = FormFields.Id ''
set @chvWhere='' WHERE FormFields.FieldName ='' + @Population 
--if @Population is not null

--set @chvWhere=@chvWhere + ''FormFields.FieldName ='' + CONVERT(varchar,@Population )
set @chvSQL = @chvSelect+ @chvFrom + @chvWhere 
select @chvSQL Query
  --if @debug = 0
        exec (@chvSQL)
  --   else
   --    select @chvSQL

END

当我执行此操作时，我得到无效的列名(``我提供给@population的任何值''实际上是formfields .fieldname是该列，我提供的值应该是该列包含的值.) h2_lin>解决方案

#Jet写道:

FormInstances内联FormInstanceData ON FormInstances.Id = FormInstanceData.FormInstanceId 内部联接社区内部联接区域

看起来错误在这里...查询格式不正确.您有多个没有联接条件的背靠背的INNER JOINS.

此外，您如何知道WHERE子句中的无效列错误.出于调试目的(捕获错误行)，建议您使用打印每组设置的行以形成查询.使用打印输出，将其连接起来，然后检查它到底在哪里显示出错误(如果您可以完全通过裸眼看！)

尝试一下...检查是否有更多逗号必需...变量值应以单引号形式传递给动态查询，以使它再次成为字符串

设置@ chvWhere =''WHERE FormFields.FieldName =''''''+ @Population +''''

如上文"S Mewara"所述

内连接似乎有问题.

i am trying to generate a dynamic where clause in my store proc.

=== @population nvarchar(max)

declare    @chvSelect varchar(max),
             @chvFrom varchar(max),
             @chvWhere varchar(max),
             @chvOrderby varchar(max),
             @chvSQL varchar(max)
BEGIN
 

--if @Population is not null

set @chvSelect=''SELECT Localities.Name,FormFields.FieldName, FormInstanceData.Value''  
set @chvFrom='' FROM   FormInstances INNER JOIN
                      FormInstanceData ON FormInstances.Id = FormInstanceData.FormInstanceId INNER JOIN
                      Communities INNER JOIN
                      Localities ON Communities.Id = Localities.Id INNER JOIN
                      WCData ON Localities.Id = WCData.LocalityId ON FormInstances.Id = WCData.FormInstanceId INNER JOIN
                      FormFields ON FormInstanceData.FieldId = FormFields.Id ''
set @chvWhere='' WHERE FormFields.FieldName ='' + @Population 
--if @Population is not null

--set @chvWhere=@chvWhere + ''FormFields.FieldName ='' + CONVERT(varchar,@Population )
set @chvSQL = @chvSelect+ @chvFrom + @chvWhere 
select @chvSQL Query
  --if @debug = 0
        exec (@chvSQL)
  --   else
   --    select @chvSQL

END

when i exec this i get invalid Column name (''whatever value i supplied to the @population'' actually the formfields .fieldname is the column and the values i supply are supposes be values contained by that column.)

解决方案

#Jet wrote:

FormInstances INNER JOIN FormInstanceData ON FormInstances.Id = FormInstanceData.FormInstanceId INNER JOIN Communities INNER JOIN Localities

Looks like error is here... Query is not properly formed. You have multiple INNER JOINS back to back without the join condition.

Furhter, how do you know the the invalid column error is in WHERE clause. For debug purpose (to catch the error line) Would suggest you to use print each line set by you for forming query. Use the print outputs, concatenate it and check where exactly it shows you wrong (if you can get through naked eyes well enough!)

Try this...check if more comma(s) required...The variable value should be passed within single quote to dynamic query in such a way that it should be again become a string

set @chvWhere='' WHERE FormFields.FieldName ='''''' + @Population + ''''

As referred above by "S Mewara"

Looks like problem with your Inner Join.

这篇关于SQSERVER 2008 EXP中的动态WHERE子句.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！