需要在组合框选择中从存储过程中选择数据 [英] Need to select data from stored procedure on combobox selection

问题描述

查找此查询

Look this query

SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE SW.sw_id=S.sw_id
AND A.equip_id = B.equip_id
And C.log_date>= @Startdate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE SW.sw_id=S.sw_id
AND A.equip_id = B.equip_id
And C.log_date>= @Startdate
And C.log_date < @EndDate
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time

我有以下查询正在检索我所需的值.
现在，我想根据组合框和日期时间选择器的选择来选择值.

来自组合框的参数是point_Location
sw_name，p_equip_id，equip_id和source_type_id

日期时间选择器中的参数为StartDate
和EndDate.

有人可以指导我编写存储过程吗?

Hi,

I have the following query which is retreiving me the required values.
Now i want to select the values based on combobox and date time picker selections.

Parameters from Combobox are point_Location
sw_name,p_equip_id,equip_id and source_type_id

Parameters from Date Time Picker are StartDate
and EndDate.

Can someone guide me to write stored procedure for the same?

推荐答案

如果您要使用临时表，请先将查询的所有数据放入临时表中，然后你在这个临时表上写你的位置条件
看起来简单的例子

if you want to use temp table then first using into put all data of your query in temp table then u write your where conditon on this temp table
look simple example

select * into #tempTable from -- now your query

--then after write your condition on these temp table
select * from #tempTable where --ur condition
-- now drop the table
drop table #tempTable 

ALTER PROCEDURE [ dbo].[Get_op_meter_data]
@Point_location VARCHAR(50)，
@sw_name varchar(30)，
@p_equip_id char(10)，
@equip_id CHAR(10)，
@Startdate datetime，
@EndDate datetime，
@source_type_id CHAR(10)

AS
声明@log_time varchar(20)，@ log_date日期时间，@ ASE_读取数字，@ G_读取数字
创建表#tmp_tt_raw_meters1(Point_location VARCHAR(50)，sw_name varchar(30)，p_equip_id char(10)，equip_id CHAR(10)，log_date日期时间，log_time VARCHAR(20)，ASE_Reading数字，G_Reading数字)
从
中选择*进入#tmp_tt_raw_meters1 (选择T.Point_location，T.sw_name，T.p_equip_id，T.equip_id，log_date，log_time，SUM(E_Reading)ASE_Reading，SUM(G_reading)AS G_Reading
从(
选择A.Point_location，SW.sw_name，A.p_equip_id，A.equip_id，CONVERT(DATE，C.log_date)AS log_date，CONVERT(VARCHAR(20)，C.log_hrs)+'':''+ CONVERT(VARCHAR( 20)，C.log_min)AS log_time，C.Act_value AS E_Reading，0 AS G_reading
从om_equipment A，om_equip_meter_map B，om_service_window SW，om_sw_location_map S，tt_raw_meter C
在哪里B.point_location = C.point_Location
AND A.equip_id = B.equip_id
AND SW.sw_id = S.sw_id
和C.log_date> = @Startdate
和C.log_date< @EndDate
AND CONVERT(VARCHAR(20)，C.log_hrs)+'':''+ CONVERT(VARCHAR(20)，C.log_min)= CONVERT(VARCHAR(20)，s.start_hrs)+'':''+ CONVERT (VARCHAR(20)，s.end_hrs)
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id =``RAW_POW''
UNION
选择A.Point_location，SW.sw_name，A.p_equip_id，A.equip_id，CONVERT(DATE，C.log_date)AS log_date，CONVERT(VARCHAR(20)，C.log_hrs)+'':''+ CONVERT(VARCHAR( 20)，C.log_min)AS log_time，0 AS E_Reading，C.Act_value AS G_reading
从om_equipment A，om_equip_meter_map B，om_service_window SW，om_sw_location_map S，tt_raw_meter C
在哪里B.point_location = C.point_Location
AND A.equip_id = B.equip_id
AND SW.sw_id = S.sw_id
和C.log_date> = @Startdate
和C.log_date< @EndDate
AND CONVERT(VARCHAR(20)，C.log_hrs)+'':''+ CONVERT(VARCHAR(20)，C.log_min)= CONVERT(VARCHAR(20)，s.start_hrs)+'':''+ CONVERT (VARCHAR(20)，s.end_hrs)
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id =``GAS'')T
GROUP BY T.Point_location，T.sw_name，T.p_equip_id，T.equip_id，log_date，log_time
ORDER BY T.Point_location，T.sw_name，T.p_equip_id，T.equip_id，log_date，log_time
)
从#tmp_tt_raw_meters1中选择*，其中Point_location = @ Point_location和sw_name = @ sw_name和p_equip_id = @ p_equip_id和equip_id = @ equip_id和(log_date< = @Startdate和> @EndDate)和ASE_Reading = @source_type_id和G_Read br/> 放置表#tmp_tt_raw_meters1



我尝试了一下，但再次给出错误.我也无法使用临时表插入和检索值.

ALTER PROCEDURE [dbo].[Get_op_meter_data]
@Point_location VARCHAR(50),
@sw_name varchar(30),
@p_equip_id char(10),
@equip_id CHAR(10),
@Startdate datetime,
@EndDate datetime,
@source_type_id CHAR(10)

AS
Declare @log_time varchar(20),@log_date datetime,@ASE_Reading numeric,@G_Reading numeric
CREATE TABLE #tmp_tt_raw_meters1(Point_location VARCHAR(50), sw_name varchar(30), p_equip_id char(10), equip_id CHAR(10), log_date datetime, log_time VARCHAR(20), ASE_Reading numeric, G_Reading numeric)
select * into #tmp_tt_raw_meters1 from
(SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ '':'' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE B.point_location = C.point_Location
AND A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
And C.log_date>= @Startdate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ '':'' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ '':'' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = ''RAW_POW''
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ '':'' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE B.point_location = C.point_Location
AND A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
And C.log_date>= @Startdate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ '':'' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ '':'' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = ''GAS'') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
)
select * from #tmp_tt_raw_meters1 where Point_location=@Point_location and sw_name=@sw_name and p_equip_id=@p_equip_id and equip_id=@equip_id and (log_date < = @Startdate and > @EndDate) and ASE_Reading= @source_type_id and G_Reading= @source_type_id
drop table #tmp_tt_raw_meters1



I tried with this but its giving error again. I am not able to insert and retreive values using temporary tables also.

这篇关于需要在组合框选择中从存储过程中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！