StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONot able to get the stored procedure's result in variable?
text
Body
copied!<p>i have a Hierarchy of stored procedures calling one in another as below:</p> <pre><code>1 2 3 </code></pre> <p>Now what i am doing is: first of all i am showing the 3rd level sp which is least level.</p> <pre><code>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 </code></pre> <p>Now the second level :</p> <pre><code>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 </code></pre> <p>Now the 3rd and final level </p> <pre><code>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 </code></pre> <p>Now what is my problem:</p> <p>I am selecting 1 as result in 3rd level which will stored in "<strong>@ResultForInsertUpdate</strong>" in second level and in 3rd level again.. </p> <p>I am getting 0 in @ResultForInsertUpdate i dont know why, please help me to resolve this prob</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload