如何使用datetime参数调用storedprocedure中的存储过程 [英] How to Call a Stored procedure within storedprocedure with datetime parameter
本文介绍了如何使用datetime参数调用storedprocedure中的存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我编写了一个名为SP_FirstDistil的存储过程，现在想要在具有相同datetime参数的SP_YieldFinal存储过程上调用它。请帮助我摆脱这个问题。 SP_FirstDistil as-
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.
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.
推荐答案
So you are trying to insert the output of the SP in a table. You inner SP is already doing that and hence you are unable to do it.
You may refer to below articles
http://www.codeproject.com/Questions/263857/An-INSERT-EXEC-statement-cannot-be-nested
http://www.sommarskog.se/share_data.html#INSERTEXEC这篇关于如何使用datetime参数调用storedprocedure中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
