Need to select data from stored procedure on combobox selection

Question

0.00/5 (No votes)

See more:

Look this query

SQL

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

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?

Posted 18-Jun-12 20:06pm

Kiran S.V

Updated 18-Jun-12 20:24pm

Prosan

v3

Add a Solution

Comments

Kiran S.V 19-Jun-12 2:24am

I mean to say write stored procedure with point_Location
sw_name, p_equip_id, equip_id and source_type_id, StartDate
and EndDate as input parameters.

And selecting the values from query.

But i don't know how to write output parameters and select the value for the above lengthy query. Can someone analyze and tell me how to proceed.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prosan · Answer 1 · 2012-06-18T20:31:00

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

SQL

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

Kiran S.V · Answer 2 · 2012-06-18T23:38:00

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.

Need to select data from stored procedure on combobox selection

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0