how to execute this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to execute this query

Author

Topic

saravananpk
Starting Member

3 Posts

Posted - 2002-07-22 : 05:56:59

exec('select a.site_id,a.system_id,@wfromdate,@logic
from eserv_equipment_utilization_data a
where @type=@value and day(a.date_time) = @dfromdate and month(a.date_time)= @mfromdate and year(a.date_time)= @yfromdate group by a.site_id,a.system_id')

here,
@wfromdate,@dfromdate,@yfromdate - datetime
@type = table column name that iam passing during execution time
#value - value to the above column name

i have to execute this in a stored procedure to which iam passing the above parameters.

this query is not working . i have declared all the parameters for the above query.

cez
Starting Member

37 Posts

Posted - 2002-07-22 : 06:14:19

Hi!

First, create a stored procedure for that query like:
CREATE PROCEDURE sp_ProcName
-- declare your input variables here
@wfromdate datetime, ...
AS
select a.site_id, a.system_id ...

Then, call this stored procedure like:
exec sp_ProcName @wfromdate, ...

hope it helps

quote:

exec('select a.site_id,a.system_id,@wfromdate,@logic
from eserv_equipment_utilization_data a
where @type=@value and day(a.date_time) = @dfromdate and month(a.date_time)= @mfromdate and year(a.date_time)= @yfromdate group by a.site_id,a.system_id')

here,
@wfromdate,@dfromdate,@yfromdate - datetime
@type = table column name that iam passing during execution time
#value - value to the above column name

i have to execute this in a stored procedure to which iam passing the above parameters.

this query is not working . i have declared all the parameters for the above query.

saravananpk
Starting Member

3 Posts

Posted - 2002-07-22 : 07:10:53

create procedure sp_proc @logic varchar(25),@type varchar(20),
@value varchar(20),@dfromdate varchar(5),@mfromdate varchar(5),
@yfromdate varchar(5) as

select a.site_id,a.system_id,@logic
from eserv_equipment_utilization_data a
where @type=@value and day(a.date_time) = @dfromdate
and month(a.date_time)= @mfromdate and year(a.date_time)
= @yfromdate group by a.site_id,a.system_id

exec sp_proc 'count(patient_id)','exam_type','EXHEAD1','13','06','2002'

when i executed the above procedure, the output was 0 rows.

but,
select a.site_id,a.system_id,count(patient_id)
from eserv_equipment_utilization_data a
where exam_type='EXHEAD1' and day(a.date_time) ='13'
and month(a.date_time)= '06' and year(a.date_time)
= '2002' group by a.site_id,a.system_id

fetches 2 records from the table when i pass the same value.

the problem is, i have to pass count(patient_id), table column name (exam_type), column value ('exhead1') and year,date and month to the above query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources