sql - Not able to get the stored procedure's result in variable? -
i have hierarchy of stored procedures calling 1 in below:
1 2 3 now doing is: first of showing 3rd level sp 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='' ) 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 scheduleid=@scheduleid , isdeleted=0 , isactive=1 select 1 now second level :
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 ) declare @resultforinsertupdate varchar(200); if convert(date,@startdatepassed,101)>convert(date,@currentdatetime,101) , convert(date,@enddatepassed,101)>convert(date,@currentdatetime,101) , convert(date,@enddatepassed,101)>convert(date,@startdatepassed,101) begin -- run when start date , end date passed greater current date , enddate greater 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 now 3rd , 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 ) -- 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 store location, area , rooms starts here create table #temptable ( rownum int, locationid bigint, areaid bigint, roomid bigint ) -- declare tempraroy table store location, area , rooms ends here if @area=0 , @room=0 begin insert #temptable (rownum,locationid,areaid,roomid) (select row_number() on (order locationid desc) rownum, locationid,areaid,roomid tblroomnew areaid in(select areaid tblareanew locationid=@location)) set @row_count=(select count(*) #temptable) set @intflag = 1 while (@intflag <=@row_count) begin -- ever want here set @locationidforloop=(select locationid #temptable rownum=@intflag) set @areaidforloop=(select areaid #temptable rownum=@intflag) set @roomidforloop=(select roomid #temptable rownum=@intflag) if @resultforinsertupdate=1 begin if exists(select 1 tbluserschedulenew convert(datetime,scheduledate,101)=convert(datetime,@oldstartdate,101) , convert(datetime,enddate,101)=convert(datetime,@oldenddate,101) , convert(time,starttime,108)=convert(time,@oldstarttime,108) , convert(time,endtime,108) =convert(time,@oldendtime,108) , contactid=@oldcontactid , location=@oldlocation , area=@oldarea , 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 , @room=0 begin insert #temptable (rownum,locationid,areaid,roomid) (select row_number() on (order locationid desc) rownum, locationid,areaid,roomid tblroomnew areaid =@area) set @row_count=(select count(*) #temptable) end else begin print 'chalan jo chal reha' exec proc_tbluserschedulenewfutureupdate @startdatepassed,@enddatepassed,@starttimepassed,@endtimepassed,@currentdatetime,@scheduleid,@contactid, @location,@area,@room,@modifiedby,@schedulefileidnew --print 'simple update' end end now problem:
i selecting 1 result in 3rd level stored in "@resultforinsertupdate" in second level , in 3rd level again..
i getting 0 in @resultforinsertupdate dont know why, please me resolve prob
possible helpful -
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 ) 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) scheduleid = @scheduleid , isdeleted = 0 , 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 ) begin if convert(date, @startdatepassed, 101) > convert(date, @currentdatetime, 101) , convert(date, @enddatepassed, 101) > convert(date, @currentdatetime, 101) , 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 ) 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 , @room = 0 begin insert #temptable (rownum, locationid, areaid, roomid) select row_number() on (order locationid desc) rownum , locationid , areaid , roomid dbo.tblroomnew a.areaid in ( select b.areaid dbo.tblareanew b b.locationid = @location ) select @row_count = count(1) , @intflag = 1 #temptable while (@intflag <= @row_count) begin select @locationidforloop = locationid , @areaidforloop = areaid , @roomidforloop = roomid #temptable rownum=@intflag if @resultforinsertupdate = 1 begin if exists ( select 1 dbo.tbluserschedulenew convert(datetime,scheduledate,101)=convert(datetime,@oldstartdate,101) , convert(datetime,enddate,101)=convert(datetime,@oldenddate,101) , convert(time,starttime,108)=convert(time,@oldstarttime,108) , convert(time,endtime,108)=convert(time,@oldendtime,108) , contactid=@oldcontactid , location=@oldlocation , area=@oldarea , 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 , @room = 0 begin insert #temptable (rownum, locationid, areaid, roomid) select row_number() on (order locationid desc) rownum , locationid , areaid , roomid dbo.tblroomnew select @row_count = count(1) #temptable end else begin exec dbo.proc_tbluserschedulenewfutureupdate @startdatepassed , @enddatepassed , @starttimepassed , @endtimepassed , @currentdatetime , @scheduleid , @contactid , @location , @area , @room , @modifiedby , @schedulefileidnew end end end 
