title: Dynamic SQL Query Search
body: <p>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</p>
  
  <pre><code>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 &lt;&gt; 0 OR @PriceMax &lt;&gt; 0 BEGIN
          SET @SQL = @SQL + ' AND PhotographerID IN(SELECT PhotographerID FROM Packages WHERE Price BETWEEN '+@PriceMin+' AND '+@PriceMax+') '
      END
  
      IF @CityID &gt; 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
  </code></pre>
  
  <p>And the error is </p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Can you describe the error? Thanks!</p>