user6628729 user6628729 - 2 months ago 6
SQL Question

Stored procedure VS query output

I try this SQL query

select
count(tblVV.PName) as total,
tblVV.PName
from
tblVV
inner join
tblRV on tblVV.MID = tblRV.ID
inner join
tblReg on tblRV.RID = tblReg.RID
where
tblReg.StartDate>= '2016-07-01 00:00:00' and
tblReg.EndDate<= '2016-07-31 23:59:59' and
tblReg.Region = 'uk' and
tblRegionVehicles_Uni.RegNo = 'BE82' and
tblVV.PName <>''
group by
tblVV.PName


This shows result like this

total PName
1 Sugar
11 Apple


Now when I create a stored procedure of same query like this

create procedure sp_ownerdata
@fromdate datetime,
@todate datetime,
@region varchar,
@RegNo varchar
as
select
count(tblVV.PName) as total,
tblVV.PName
from
tblVV
inner join
tblRV on tblVV.MID = tblRV.ID
inner join
tblReg on tblRV.RID = tblReg.RID
where
tblReg.StartDate >= @fromdate and
tblReg.EndDate <= @todate and
tblReg.Region = @region and
tblRegionVehicles_Uni.RegNo = @RegNo and
tblVV.PName <>''
group by
tblVV.PName


and execute like this

execute sp_ownerdata '2016-07-01 00:00:00','2016-07-31 23:59:59','uk','BE82'


then this shows nothing where as I write correct parameters name and correct values I enter when I execute the stored procedure

total PName

Answer

Problem in parameters length

@region varchar,
@RegNo varchar

You need to specify proper length for VARCHAR parameters in stored procedure

@region varchar(10),
@RegNo varchar(10)