I have this query,
Declare @Prcocessrate float
declare @item varchar(20)
declare @process varchar(20)
declare @branch varchar(20)
set @item = 'shirt'
set @process = 'kt'
set @branch = '1'
select @Prcocessrate = ProcessPrice from itemwiseprocessrate where itemname=@Item and Process=@process and branchid=@branch
when I run it single handed, the execution plan only shows 3 steps, see for youself..
but I have this procedure
sp_newBooking
as
ALTER PROC sp_newbooking
-- other arguements--
AS
BEGIN
--OTHER FLAGS--
ELSE IF (@Flag = 32)
BEGIN
declare @ItemId varchar(max),@ProcessRate float
--set @BranchId='1'
select @ProcessCode = DefaultProcessCode from mstconfigsettings where branchid=@BranchId
select @ItemId= DefaultItemId from mstconfigsettings where branchid=@BranchId
select @ItemName= ItemName from itemmaster where itemid=@ItemId and branchid=@BranchId
select @ProcessRate = ProcessPrice from itemwiseprocessrate where itemname=@ItemName and ProcessCode=@ProcessCode and branchid=@BranchId
if(@ProcessRate is not null)
select @ItemName as ItemName,@ProcessCode as ProcessCode,@ProcessRate as ProcessRate
else
select @ItemName as ItemName,@ProcessCode as ProcessCode,'0' as ProcessRate
END
-- OTHER FLAGS --
END
Now!, when I run this
exec sp_newbooking
@flag = 32,
@Branchid = 1
The execution plan is showing 6 steps! Here's the picture..!
See the query 4
Why is it taking 6 steps to perform the same query when executing from the procedure, while its taking 3 steps when executing alone? Wtf is this?