sql server - stored procedure passing date parameter getting error

Question

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

I am trying to execute like this:

exec ParkingDeatailsReport 5, '2013-01-01 00:00:00','2013-06-18 23:59:59'

but, I'm getting an error:Must declare the scalar variable "@startdate".

Answer 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:

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