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".
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…