[Solved] stored procedure passing date parameter getting error

by


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:

ALTER PROCEDURE [dbo].[ParkingDeatailsReport] 
  @locid INTEGER, 
  @startdate nvarchar(100),
  @enddate nvarchar(100)
as
BEGIN
  DECLARE @cols AS NVARCHAR(MAX),
          @query  AS NVARCHAR(MAX)

  SELECT @cols = STUFF((SELECT distinct ',' + 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(@locid as varchar(max)) + '  
                ) d 
                PIVOT ( count(Vtype)     
                FOR Vtype in (' + @cols + ')  ) p '

  EXECUTE(@query)
END

1

solved stored procedure passing date parameter getting error