无法在变量中获取存储过程的结果?
我有一个存储过程的层次结构，如下所示:
i have a Hierarchy of stored procedures calling one in another as below:
1
2
3
现在我正在做的是:首先，我展示的是最低级别的第 3 级 sp.
Now what i am doing is: first of all i am showing the 3rd level sp which is least level.
CREATE proc [dbo].[proc_tblUserScheduleNewUpdateOnly]
(
@Scheduleid bigint=258,
@Contactid uniqueidentifier='EDE3E474-02CA-49C7-86DD-AA97794ECF8A',
@ScheduleDate datetime= '2012-07-16 00:00:00.000',
@StartTime varchar(20)='12:03:00.0000000',
@EndTime varchar(20)='15:00:00.0000000',
@Location bigint=8,
@Area bigint=7,
@Room bigint=9,
@Enddate datetime='2012-07-16 00:00:00.000',
@CurrentTime Datetime='2012-07-16 12:00:35.900',
@ModifiedBy uniqueidentifier='0BF84A77-FAC2-44E5-AF9B-39740415DBD2',
@schedulefileidNew bigint=''
)
as
Declare @schedulefileid bigint
if @schedulefileidNew=0
begin
set @schedulefileid=null
end
else
begin
set @schedulefileid=@schedulefileidNew
end
update tblUserScheduleNew set Contactid=@Contactid,
ScheduleDate =@ScheduleDate,
StartTime = @StartTime,
EndTime =@EndTime,
Location =@Location,
Area=@Area,
Room =@Room,
LastModifiedDate=@CurrentTime ,EndDate=@Enddate,
ModifiedBy=@ModifiedBy,
ScheduleFileId=@schedulefileid
where ScheduleId=@Scheduleid and IsDeleted=0 and isActive=1
select 1
现在是第二级:
CREATE Proc [dbo].[proc_tblUserScheduleNewFutureUpdate]
(
@StartDatePassed datetime='8/2/2012 12:00:00 AM',
@EndDatePassed datetime='8/2/2012 12:00:00 AM', --='2012-07-11 00:00:00.000',
@StartTimePassed varchar(20)='13:00:00',--='02:00:00.0000000',
@EndTimePassed varchar(20)='21:00:00',--='03:00:00.0000000',
@CurrentDateTime Datetime ='8/1/2012 5:50:31 AM', --='2012-07-11 02:07:35.900'
@Scheduleid bigint=0x0000000000000166,
@Contactid uniqueidentifier='77680636-bc4b-4489-9cec-3bc000ffe773',
@Location bigint=11,
@Area bigint=10,
@Room bigint=11,
@ModifiedBy uniqueidentifier='acf7961c-4111-49ad-a66a-ce7f9ce131bd',
@schedulefileidNew bigint=null
)
as
declare @ResultForInsertUpdate varchar(200);
if CONVERT(date,@StartDatePassed,101)>CONVERT(date,@CurrentDateTime,101) and
CONVERT(date,@EndDatePassed,101)>CONVERT(date,@CurrentDateTime,101) and
CONVERT(date,@EndDatePassed,101)>CONVERT(date,@StartDatePassed,101)
begin -- it will run when the Start date and end date passed are greater than Current date and EndDate is greater than Start date.
Print 'Update'
exec @ResultForInsertUpdate = dbo.proc_tblUserScheduleNewUpdateOnly @Scheduleid,@Contactid,@StartDatePassed,@StartTimePassed,@EndTimePassed,@Location,@Area,@Room,@EndDatePassed,@CurrentDateTime,@ModifiedBy,@schedulefileidNew
select @ResultForInsertUpdate;
end
else
begin
select 2
end
现在是第 3 级和最后一级
Now the 3rd and final level
Alter Proc proc_tblUserScheduleNewUpdateWithAllRoomsOption
(
@StartDatePassed datetime='2013-04-29 00:00:00.000',
@EndDatePassed datetime='2013-04-29 00:00:00.000',
@StartTimePassed varchar(20)='15:00:00',
@EndTimePassed varchar(20)='20:00:00',
@CurrentDateTime Datetime ='2013-04-25 00:00:00.000',
@Scheduleid bigint=1,
@Contactid uniqueidentifier='FD3E0DDF-8B91-493F-94DF-B8280AC33BC0',
@Location bigint=17,
@Area bigint=0,
@Room bigint=0,
@ModifiedBy uniqueidentifier='ACF7961C-4111-49AD-A66A-CE7F9CE131BD',
@schedulefileidNew bigint=null,
@OldStartDate Datetime='2013-04-26 00:00:00.000',
@OldEndDate DateTime='2013-04-26 00:00:00.000',
@OldStartTime varchar(20)='11:11:11.0000000',
@OldEndTime varchar(20)='22:22:22.0000000',
@OldContactid uniqueidentifier='DA101C1D-45A1-4F9A-B19B-4E88DDE01B10',
@OldLocation bigint=18,
@OldArea bigint=17,
@OldRoom bigint=22
)
as
-- declare variables Starts here
declare @row_count int;
DECLARE @intFlag INT=0;
declare @locationIdForLoop bigint ;
declare @AreaIdForLoop bigint ;
declare @RoomIdForLoop bigint ;
DECLARE @ResultForInsertUpdate INT
set @ResultForInsertUpdate=1;
-- declare tempraroy table to store location, Area and rooms Starts here
CREATE TABLE #tempTable (
RowNum int,
LocationId bigint,
AreaId bigint,
RoomId bigint
)
-- declare tempraroy table to store location, Area and rooms Ends here
if @Area=0 and @Room=0
begin
insert into #tempTable (RowNum,LocationId,AreaId,RoomId) (select ROW_NUMBER() OVER
(ORDER BY LocationId desc) RowNum, LocationId,AreaId,RoomId from
tblroomnew where areaid in(select Areaid from tblareanew where locationid=@Location))
set @row_count=(select count(*) from #tempTable)
SET @intFlag = 1
WHILE (@intFlag <=@row_count)
BEGIN
-- Do what ever you want to do here
set @locationIdForLoop=(select locationid from #tempTable where RowNum=@intFlag)
set @AreaIdForLoop=(select areaid from #tempTable where RowNum=@intFlag)
set @RoomIdForLoop=(select roomid from #tempTable where RowNum=@intFlag)
if @ResultForInsertUpdate=1
begin
if exists(select 1 from tbluserschedulenew where
convert(datetime,ScheduleDate,101)=convert(datetime,@OldStartDate,101) and
Convert(datetime,EndDate,101)=convert(datetime,@OldEndDate,101) and
convert(Time,StartTime,108)=convert(Time,@OldStartTime,108) and
convert(Time,EndTime,108) =convert(Time,@OldEndTime,108) and contactid=@OldContactid
and
Location=@OldLocation and Area=@OldArea and Room=@OldRoom )
begin
Print 'Update First record'
exec @ResultForInsertUpdate = proc_tblUserScheduleNewFutureUpdate @StartDatePassed,@EndDatePassed,@StartTimePassed,@EndTimePassed,@CurrentDateTime,@Scheduleid,@Contactid,
@locationIdForLoop,@AreaIdForLoop,@RoomIdForLoop,@ModifiedBy,@schedulefileidNew
--set @ResultForInsertUpdate=1
print @ResultForInsertUpdate
--select @ResultForInsertUpdate
end
else
begin
print 'insert karna hai record'
exec proc_tblUserScheduleNewLatestInsert @Contactid,@StartDatePassed,@StartTimePassed,@EndTimePassed,
@locationIdForLoop,@AreaIdForLoop,@RoomIdForLoop, @EndDatePassed,@ModifiedBy,0,@CurrentDateTime
--print @ResultForInsertUpdate
end
end
else
begin
select @ResultForInsertUpdate
end
SET @intFlag = @intFlag + 1
END
end
else
begin
if @Area!=0 and @Room=0
begin
insert into #tempTable (RowNum,LocationId,AreaId,RoomId) (select ROW_NUMBER() OVER (ORDER BY LocationId desc) RowNum, LocationId,AreaId,RoomId from
tblroomnew where areaid =@Area)
set @row_count=(select count(*) from #tempTable)
end
else
begin
print 'chalan do jo chal reha'
exec proc_tblUserScheduleNewFutureUpdate @StartDatePassed,@EndDatePassed,@StartTimePassed,@EndTimePassed,@CurrentDateTime,@Scheduleid,@Contactid,
@location,@Area,@Room,@ModifiedBy,@schedulefileidNew
--print 'simple update'
end
end
现在我的问题是什么:
我选择 1 作为第三级的结果，它将存储在第二级的@ResultForInsertUpdate"中，然后再次存储在第三级..
I am selecting 1 as result in 3rd level which will stored in "@ResultForInsertUpdate" in second level and in 3rd level again..
我在@ResultForInsertUpdate 中得到 0 我不知道为什么，请帮我解决这个问题
I am getting 0 in @ResultForInsertUpdate i dont know why, please help me to resolve this prob
推荐答案
也许这对你有帮助 -
Possible this be helpful for you -
1.
CREATE PROCEDURE [dbo].[proc_tblUserScheduleNewUpdateOnly]
(
@Scheduleid BIGINT
, @Contactid UNIQUEIDENTIFIER
, @ScheduleDate DATETIME
, @StartTime VARCHAR(20)
, @EndTime VARCHAR(20)
, @Location BIGINT
, @Area BIGINT
, @Room BIGINT
, @Enddate DATETIME
, @CurrentTime DATETIME
, @ModifiedBy UNIQUEIDENTIFIER
, @schedulefileidNew BIGINT
)
AS BEGIN
UPDATE dbo.tblUserScheduleNew
SET
Contactid = @Contactid
, ScheduleDate = @ScheduleDate
, StartTime = @StartTime
, EndTime = @EndTime
, location = @Location
, Area = @Area
, Room = @Room
, LastModifiedDate = @CurrentTime
, EndDate = @Enddate
, ModifiedBy = @ModifiedBy
, ScheduleFileId = NULLIF(@schedulefileidNew, 0)
WHERE ScheduleID = @Scheduleid
AND IsDeleted = 0
AND isActive = 1
RETURN 1
END
2.
CREATE PROCEDURE [dbo].[proc_tblUserScheduleNewFutureUpdate]
(
@StartDatePassed DATETIME
, @EndDatePassed DATETIME
, @StartTimePassed VARCHAR(20)
, @EndTimePassed VARCHAR(20)
, @CurrentDateTime DATETIME
, @Scheduleid BIGINT
, @Contactid UNIQUEIDENTIFIER
, @Location BIGINT
, @Area BIGINT
, @Room BIGINT
, @ModifiedBy UNIQUEIDENTIFIER
, @schedulefileidNew BIGINT
)
AS BEGIN
IF
CONVERT(DATE, @StartDatePassed, 101) > CONVERT(DATE, @CurrentDateTime, 101)
AND
CONVERT(DATE, @EndDatePassed, 101) > CONVERT(DATE, @CurrentDateTime, 101)
AND
CONVERT(DATE, @EndDatePassed, 101) > CONVERT(DATE, @StartDatePassed, 101)
BEGIN
DECLARE @ResultForInsertUpdate VARCHAR(200)
EXEC @ResultForInsertUpdate = dbo.proc_tblUserScheduleNewUpdateOnly
@Scheduleid
, @Contactid
, @StartDatePassed
, @StartTimePassed
, @EndTimePassed
, @Location
, @Area
, @Room
, @EndDatePassed
, @CurrentDateTime
, @ModifiedBy
, @schedulefileidNew
RETURN @ResultForInsertUpdate
END
ELSE BEGIN
RETURN 2
END
END
3.
CREATE PROCEDURE proc_tblUserScheduleNewUpdateWithAllRoomsOption
(
@StartDatePassed DATETIME,
@EndDatePassed DATETIME,
@StartTimePassed VARCHAR(20),
@EndTimePassed VARCHAR(20),
@CurrentDateTime DATETIME,
@Scheduleid BIGINT,
@Contactid UNIQUEIDENTIFIER,
@Location BIGINT,
@Area BIGINT,
@Room BIGINT,
@ModifiedBy UNIQUEIDENTIFIER,
@schedulefileidNew BIGINT,
@OldStartDate DATETIME,
@OldEndDate DATETIME,
@OldStartTime VARCHAR(20),
@OldEndTime VARCHAR(20),
@OldContactid UNIQUEIDENTIFIER,
@OldLocation BIGINT,
@OldArea BIGINT,
@OldRoom BIGINT
)
AS BEGIN
DECLARE
@row_count INT
, @intFlag INT = 0
, @locationIdForLoop BIGINT
, @AreaIdForLoop BIGINT
, @RoomIdForLoop BIGINT
, @ResultForInsertUpdate INT = 1
CREATE TABLE #tempTable (RowNum INT, LocationId BIGINT, AreaId BIGINT, RoomId BIGINT)
IF @Area = 0 AND @Room = 0 BEGIN
INSERT INTO #tempTable (RowNum, LocationId, AreaId, RoomId)
SELECT
ROW_NUMBER() OVER (ORDER BY LocationId DESC) RowNum
, LocationId
, AreaId
, RoomId
FROM dbo.tblroomnew a
WHERE a.AreaId IN (
SELECT b.AreaId
FROM dbo.tblareanew b
WHERE b.LocationId = @Location
)
SELECT
@row_count = COUNT(1)
, @intFlag = 1
FROM #tempTable
WHILE (@intFlag <= @row_count) BEGIN
SELECT
@locationIdForLoop = LocationId
, @AreaIdForLoop = AreaId
, @RoomIdForLoop = RoomId
FROM #tempTable
WHERE RowNum=@intFlag
IF @ResultForInsertUpdate = 1 BEGIN
IF EXISTS (
SELECT 1
FROM dbo.tbluserschedulenew
WHERE
CONVERT(DATETIME,ScheduleDate,101)=CONVERT(DATETIME,@OldStartDate,101) AND
CONVERT(DATETIME,EndDate,101)=CONVERT(DATETIME,@OldEndDate,101) AND
CONVERT(TIME,StartTime,108)=CONVERT(TIME,@OldStartTime,108) AND
CONVERT(TIME,EndTime,108)=CONVERT(TIME,@OldEndTime,108) AND contactid=@OldContactid
AND location=@OldLocation
AND Area=@OldArea
AND Room=@OldRoom
)
BEGIN
PRINT 'Update First record'
EXEC @ResultForInsertUpdate=proc_tblUserScheduleNewFutureUpdate
@StartDatePassed
, @EndDatePassed
, @StartTimePassed
, @EndTimePassed
, @CurrentDateTime
, @Scheduleid
, @Contactid
, @locationIdForLoop
, @AreaIdForLoop
, @RoomIdForLoop
, @ModifiedBy
, @schedulefileidNew
RETURN @ResultForInsertUpdate
END
ELSE
BEGIN
EXEC proc_tblUserScheduleNewLatestInsert
@Contactid
, @StartDatePassed
, @StartTimePassed
, @EndTimePassed
, @locationIdForLoop
, @AreaIdForLoop
, @RoomIdForLoop
, @EndDatePassed
, @ModifiedBy
, 0
, @CurrentDateTime
END
END
ELSE BEGIN
RETURN @ResultForInsertUpdate
END
SELECT @intFlag = @intFlag + 1
END
END
ELSE
BEGIN
IF @Area != 0 AND @Room = 0 BEGIN
INSERT INTO #tempTable (RowNum, LocationId, AreaId, RoomId)
SELECT
ROW_NUMBER() OVER (ORDER BY LocationId DESC) RowNum
, LocationId
, AreaId
, RoomId
FROM dbo.tblroomnew a
SELECT @row_count = COUNT(1)
FROM #tempTable
END
ELSE BEGIN
EXEC dbo.proc_tblUserScheduleNewFutureUpdate
@StartDatePassed
, @EndDatePassed
, @StartTimePassed
, @EndTimePassed
, @CurrentDateTime
, @Scheduleid
, @Contactid
, @location
, @Area
, @Room
, @ModifiedBy
, @schedulefileidNew
END
END
END
相关文章