Stored Procedure Passing Date Parameter Getting Error

alter procedure [dbo].[ParkingDeatailsReport] @locid INTEGER, @startdate nvarchar(100),@enddate nvarchar(100) as begin DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(M

Solution 1:

The problem is that you are building SQL in the proc, but you are not using the values in @startdate and @enddate, instead you are passing the string

You need to grab the values of these variables when you build the string - something like:

ALTERPROCEDURE [dbo].[ParkingDeatailsReport] 
  @locidINTEGER, 
  @startdate nvarchar(100),
  @enddate nvarchar(100)
asBEGINDECLARE@colsAS NVARCHAR(MAX),
          @queryAS NVARCHAR(MAX)

  SELECT@cols= STUFF((SELECTdistinct','+ QUOTENAME(Vtype)    
                       from VType_tbl FOR XML PATH(''), 
                       TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

  SET@query='SELECT Date, '+@cols+' 
                FROM  ( 
                  SELECT 
                    v.Vtype, 
                    convert(date, dtime) as Date 
                  FROM Transaction_tbl t 
                  INNER JOIN VType_tbl v  
                  ON t.vtid = v.vtid 
                  WHERE 
                    dtime between '''+@startdate+''' and '''+@enddate+''' 
                  AND locid =  '+CAST(@locidasvarchar(max)) +'  
                ) d 
                PIVOT ( count(Vtype)     
                FOR Vtype in ('+@cols+')  ) p 'EXECUTE(@query)
END