How to Call a Stored procedure within storedprocedure with datetime parameter

See more:

SQL

I write one stored procedure named as SP_FirstDistil now wants to call it on SP_YieldFinal stored procedure with same datetime Parameter. Kindly help me out from this problem. SP_FirstDistil as-

USE [cheminova]
GO
/****** Object:  StoredProcedure [dbo].[SP_Firstdistil]    Script Date: 10/27/2014 12:54:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Pragya>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_Firstdistil] 
    -- Add the parameters for the stored procedure here
    --   @StartDate varchar(50)=Null,
       --@EndDate varchar(50)=Null
       @SDate DateTime
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @StartDate varchar(50)
    declare @EndDate varchar(50)
    set @EndDate=CONVERT(varchar,DATEPART(month,@SDate))+'/'+CONVERT(varchar,DATEPART(DAY,@SDate))+'/'+CONVERT(varchar,DATEPART(YEAR,@SDate))+' 06:00:00'
    set @StartDate=CONVERT(varchar,DATEPART(month,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(DAY,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(YEAR,DATEADD(day,-1,@SDate)))+' 06:00:00'
    Begin
    --table to store data of each tag for a day
    create table #tempval
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        tagname varchar(100),
        [timestamp] Varchar(50),
        tagval varchar(50),
        quality varchar(20)
    )
    create table #tempval1
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        tagname varchar(100),
        [timestamp] Varchar(50),
        tagval varchar(50),
        quality varchar(20)
    )
    create table #tempval2
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        tagname varchar(100),
        [timestamp] Varchar(50),
        tagval varchar(50),
        quality varchar(20)
    )
    create table #tempval3
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        tagname varchar(100),
        [timestamp] Varchar(50),
        tagval varchar(50),
        quality varchar(20)
    )
    --table to store average of each tag for a day
    create table #tagavg
    (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    avgtag varchar(100)
    )
                create table #temp1
                (
                [ID] [int] IDENTITY(1,1) NOT NULL,
                --[Timestamp] varchar(50),
                [Value][float] NULL
                )
                create table #temp2
                (
                [ID] [int] IDENTITY(1,1) NOT NULL,
                --[Timestamp] varchar(50),
                [Value][float] NULL
                )
                create table #temp3
                (
                [ID] [int] IDENTITY(1,1) NOT NULL,
                --[Timestamp] varchar(50),
                [Value][float] NULL
                )
                create table #temp4
                (
                [ID] [int] IDENTITY(1,1) NOT NULL,
                --[Timestamp] varchar(50),
                [Value][float] NULL
                )
                create table #temp5
                (
                [ID] [int] IDENTITY(1,1) NOT NULL,
                --[Timestamp] Datetime,
                [Value][float] NULL
                )
    create table #tempdigival
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [timestamp] varchar(30),
        tagval varchar(30)
    )

    create table #tempsum
    (
        tagval varchar(30),
        calevent float
    )

    create table #tagtemp
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [timestamp] datetime,
        tagname varchar(200),
        descrip varchar(200),
        unit varchar(10),
        tagval varchar(38),
        calevent float
    )
    create table #totalizervalue
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        tagname varchar(200),
        waqt varchar(30),
        tagval varchar(30)
    )
    --to store reading
    --select @StartDate as startdate,@EndDate as enddate
     DECLARE @SQL as varchar(1000)
    Declare @Tagname varchar(300)
    Declare @tagVal float
    Declare @calval float
    declare @avgtag varchar(100)
    declare @X1 float
    declare @Y1 float
    declare @Z1 float
    declare @X2 float
    declare @Y2 float
    declare @Z2 float
    declare @X3 float
    declare @Y3 float
    declare @Z3 float
    declare @X4 float
    declare @Y4 float
    declare @Z4 float
    declare @X5 float
    declare @Y5 float
    declare @Z5 float
    declare @X6 float
    declare @Y6 float
    declare @Z6 float
    declare @X7 float
    declare @Y7 float
    declare @Z7 float
    declare @X8 float
    declare @Y8 float
    declare @Z8 float
    declare @query varchar(500)
    declare @starttime varchar(30)
    declare @endtime varchar(30)
    declare @coltagname varchar(300)
    declare @coltimestamp datetime
    declare @colavg varchar(38)
    declare @prevtime varchar(30)
    declare @currtime varchar(30)
    declare @breakflag int
    declare @firsttimeflag int
    set @firsttimeflag=0
    declare @final float
    declare @initial float
    declare @event float
    Declare @digitalsql varchar(1000)
    set @final=0
    set @initial=0
     set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
     set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime    
     SELECT tagname, timestamp  ,value,quality FROM ihRawData 
     WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1302'''' '')'
     INSERT INTO #tempval1 EXEC(@SQL)
     Set @Tagname='ADMIN-PC.Channel5.Device1.LIC1302'
        --Select @Tagname as TagName, [timestamp],CAST(tagval as float)as TagValue,
        --Cast(tagval as float)*11.08 as CalValue ,quality from
        --#tempval1 ORDER BY tagname, [timestamp]
        select top 1 @X1= Cast(tagval as float) from #tempval1 order by ID asc
        --select @X1 as FirstVal        
        select top 1 @Y1=Cast(tagval as Float) from #tempval1 order by ID Desc
        --Select @Y1 as SecondVal
        set @Z1=(@Y1-@X1)*11.08
        Insert into #temp1 values (@Z1)
        --SELECT @Z1 as LI1603 from #temp1

     set @sql ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
     set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=Calculated    
     SELECT tagname, timestamp  ,value,quality FROM ihRawData 
     WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1304'''' '')'
     INSERT INTO #tempval2 EXEC(@SQL)
       /*Select  @Tagname as TagName,[timestamp],CAST(tagval as float)as TagValue,
        Cast(tagval as float)*6.02 as CalValue ,quality from
     #tempval2 ORDER BY tagname, [timestamp]*/
     select top 1 @X2= Cast (tagval as Float) from #tempval2 order by ID asc
        select top 1 @Y2=Cast (tagval as Float) from #tempval2 order by ID desc
        set @Z2=(@Y2-@X2)*6.02
        Insert into #temp2 values (@Z2)
        --SELECT @Z2 as LI1604 from #temp2

     set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
     set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime    
     SELECT tagname, timestamp  ,value,quality FROM ihRawData 
     WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1403'''' '')'
     INSERT INTO #tempval3 EXEC(@SQL)
     SET @Tagname='ADMIN-PC.Channel5.Device1.LIC1403' 
       /* Select @Tagname as TagName,[timestamp],CAST(tagval as float)as TagValue,
        Cast(tagval as float)*4.80 as CalValue ,quality from
        #tempval3 ORDER BY tagname, [timestamp]*/
        select top 1 @X3= Cast (tagval as Float) from #tempval3 order by ID asc
        --select @X3 as FirstValue
        Select top 1 @Y3=Cast (tagval as Float) from #tempval3 order by ID Desc
        --select @Y3 as secondvalue
        set @Z3=(@Y3-@X3)*4.80
        Insert into #temp3 values (@Z3)
        --SELECT @Z3 as LI1607 from #temp3

        set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.P65'')'
        insert into #tempdigival exec(@query)
        select @breakflag= COUNT(*) from #tempdigival where tagval=1
        set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select tagname,timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.LI1505'')'     
        insert into #totalizervalue exec(@query)
        --take lost time from manual
        if (@breakflag>0)       
        begin
            declare C1 cursor
            for select timestamp from #tempdigival where tagval=1
            open C1
            fetch next from C1 into @currtime
            while @@FETCH_STATUS=0
            begin
                if (@firsttimeflag=0)
                begin
                    set @firsttimeflag=1
                select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime)))
                    select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc
                    set @final=(@final-@initial) 
                    set @event=@final * 10
                    set @colavg=CONVERT(varchar,@event)
                    insert into #tempsum values(@colavg,@final) 
                    set @prevtime=@currtime
                end
                else
                begin
                    select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime)
                    select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc
                    set @final=(@final-@initial)
                    set @colavg=CONVERT(varchar,@final)
                    insert into #tempsum values(@colavg,@final) 
                    set @prevtime=@currtime
                end
                fetch next from C1 into @currtime
            end
            select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime)
            select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc
            set @final=(@final-@initial)
            set @event=@final * 10
            set @colavg=CONVERT(varchar,@event)
            insert into #tempsum values(@colavg,@final) 
            close C1
            deallocate C1
            select @colavg=SUM(CAST(tagval as float)) from #tempsum
            --truncate table #tempsum
            set @coltagname='ADMIN-PC.Channel5.Device1.LIC1503'
            set @coltimestamp=CONVERT(datetime,@endtime)
            insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final)
        end
        else
        begin
            select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime)))
            select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc    
            set @final=(@final-@initial)
            set @event=@final * 10
            set @colavg=CONVERT(varchar,@event)
            set @coltagname='ADMIN-PC.Channel5.Device1.LI1505'
            set @coltimestamp=CONVERT(datetime,@endtime)
            insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final)
        end
        --SELECT @Z4 as LI1608 from #temp4 

     set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
     set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime    
     SELECT tagname, timestamp  ,value,quality FROM ihRawData 
     WHERE tagname =''''ADMIN-PC.Channel5.Device1.FIQ1302'''' '')'
     INSERT INTO #tempval EXEC(@SQL)
     --SET @Tagname='ADMIN-PC.Chem.Device1.LI1609'
     --   Select @Tagname,[timestamp],CAST(tagval as float)as TagValue,
     --   Cast(tagval as float)*73.30 as CalValue ,quality from
     --#tempval ORDER BY tagname, [timestamp]
     --select @calval=Cast (tagval as Float)*73.30 from #tempval
     --   Insert into #tagtemp values (@calval,@tagVal)
        select top 1 @X5= Cast (tagval as Float) from #tempval order by ID asc
        --select @X5 as FirstValue
        Select top 1 @Y5=Cast (tagval as Float) from #tempval order by ID Desc
        --select @Y5 as secondvalue
        set @Z5=(@Y5-@X5)*73.30
        Insert into #temp5 values (@Z5)
        --SELECT @Z5 as LI1609 from #temp5
     END
     set @avgtag=(@Z1+@Z2+@Z3+@Z4)/nullif((@Z5),0)
     insert into #tagavg values (@avgtag)
     --inner join on table
     Begin
     select isnull(t1.Value,0) as  R_21LT,isnull(t2.Value,0) as  R_22LT,isnull(t3.Value,0) as R_24LT, isnull(t4.calevent,0) as B_22LT,isnull(t5.Value,0) as DETA_FIQ ,isnull(t6.avgtag,0) as avgtag from #temp1 as t1
     Inner join #temp2 as t2
     on t2.ID=t1.ID
     Inner join #temp3 as t3
     on t3.ID=t2.ID
     Inner join #tagtemp as t4
     on t4.ID=t3.ID
     inner join #temp5 as t5
     on t5.ID=t4.ID
     inner join #tagavg as t6
     on t6.ID=t5.ID
     End

END

I tried this solution also- exec dbo.SP_FirstDistil @SDate
and i tried also to convert stored procedure into function but function dosn't support to store temporary table Please help.