动态SQL查询搜索 [英] Dynamic SQL Query Search

问题描述

我正在尝试使用给定的参数构建SQL查询，但出现一个奇怪的错误，无法理解原因。这是我的SP和结果

I'm trying to build an SQL query with given params, but I get a weird error and cant understand why. Here is my SP and result

ALTER PROCEDURE [dbo].[sp_Photographers_Select_Search]
    @Date varchar(100),
    @PriceMin int,
    @PriceMax int,
    @CityID int

AS
BEGIN
    SET DATEFORMAT DMY
    DECLARE @SQL as varchar(2000)
    SET @SQL = 'SELECT *,
     (SELECT TOP (1) Price FROM  Packages WHERE PhotographerID = Photographers.PhotographerID ORDER BY Price) as PriceMin,
     (SELECT TOP (1) Price FROM  Packages WHERE PhotographerID = Photographers.PhotographerID ORDER BY Price DESC) as PriceMax,
     (SELECT COUNT(GalleryID) FROM Galleries WHERE PhotographerID = Photographers.PhotographerID AND Status = 1) as GalleryCount,
     (SELECT COUNT(CommentID) FROM Comments WHERE ContentID = Photographers.PhotographerID AND Status = 1 AND TypeID = 1) as CommentCount
     FROM Photographers WHERE 1 = 1  '

    IF @PriceMin <> 0 OR @PriceMax <> 0 BEGIN
        SET @SQL = @SQL + ' AND PhotographerID IN(SELECT PhotographerID FROM Packages WHERE Price BETWEEN '+@PriceMin+' AND '+@PriceMax+') '
    END

    IF @CityID > 0
        SET @SQL += ' AND CityID = '+@CityID+'' 

    SET @SQL = @SQL + ' AND  PhotographerID NOT IN (SELECT PhotographerID FROM Appointments WHERE Date = '''+@Date+''')'    

    EXEC (@SQL)
END

EXEC    @return_value = [dbo].[sp_Photographers_Select_Search]
        @Date = N'23.05.2013',
        @PriceMin = 0,
        @PriceMax = 0,
        @CityID = 34

错误是

Msg 245, Level 16, State 1, Procedure sp_Photographers_Select_Search, Line 23
Conversion failed when converting the varchar value 'SELECT *,
     (SELECT TOP (1) Price FROM  Packages WHERE PhotographerID = Photographers.PhotographerID ORDER BY Price) as PriceMin,
     (SELECT TOP (1) Price FROM  Packages WHERE PhotographerID = Photographers.PhotographerID ORDER BY Price DESC) as PriceMax,
     (SELECT COUNT(GalleryID) FROM Galleries WHERE PhotographerID = Photographers.PhotographerID AND Status = 1) as GalleryCount,
     (SELECT COUNT(CommentID) FROM Comments WHERE ContentID = Photographers.PhotographerID AND Status = 1 AND TypeID = 1) as CommentCount
     FROM Photographers WHERE 1 = 1  ' to data type int.

您能描述这个错误吗？谢谢！

Can you describe the error? Thanks!

推荐答案

Cast 数值即@PriceMin，@PriceMax

Cast the numeric values i.e. @PriceMin , @PriceMax and @CityID before concatenating in sql statement.

修改后的sql语句如下：

Modified sql statement is as per below:

IF @PriceMin <> 0 OR @PriceMax <> 0 
BEGIN
    SET @SQL = @SQL + ' AND PhotographerID IN(SELECT PhotographerID FROM Packages WHERE Price BETWEEN '+ cast(@PriceMin as varchar(10)) 
    +' AND '+ cast(@PriceMax as varchar(10)) +') '
END

IF @CityID > 0
   SET @SQL += ' AND CityID = '+ cast(@CityID as varchar(10) )

这篇关于动态SQL查询搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！