i'm trying below code work. if hard code "where ip.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = 'tobacco')" stored procedure works want pass @riskfactortype "where ip.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = @riskfactortype)". possible?
create type typeriskfactorstable table (riskfactortype varchar(30), riskfactoricd9 varchar(10)) go declare @riskfactors typeriskfactorstable go create procedure dflt.getriskfactors @riskfactoricd9 typeriskfactorstable readonly, @riskfactortype typeriskfactorstable readonly declare @startdate datetime declare @enddate datetime set @startdate = '2014-01-01 00:00:00' set @enddate = '2015-12-31 23:59:59' select x.* [dflt].[riskfactorsx] (select c.id, ip.admitdatetime datetime, ip.sta3n, ip.icd9code, ip.icd9description, 'ip' ds dbo.inpat_inpatient ip inner join dbo.cohort c on (c.id = ip.id) ip.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = @riskfactortype) , ip.admitdatetime between @startdate , @enddate union select c.id, ov.visitdatetime datetime, ov.sta3n, ov.icd9code, ov.icd9description, 'op' ds dbo.outpat_visit ov inner join dbo.cohort c on (c.id = ov.id) ov.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = @riskfactortype) , ov.visitdatetime between @startdate , @enddate ) x order id, datetime, ds go declare @riskfactors typeriskfactorstable insert @riskfactors(riskfactortype,riskfactoricd9) values ('tobacco', '305.1'), ('tobacco', '989.84'), ('tobacco', 'v15.82'), ('pvd', '443.9'), ('ptsd', '309.81'), ('hypotension', '458.0'), ('hypotension', '458.1'), ('hypotension', '458.2'), exec dflt.getriskfactors @riskfactortype = 'tobacco' go @mark t @eug working code... thanks.
create type typeriskfactorstable table (riskfactortype varchar(30), riskfactoricd9 varchar(10)) go create procedure dflt.getriskfactors @riskfactoricd9 typeriskfactorstable readonly, @riskfactortype varchar(30) declare @startdate datetime declare @enddate datetime set @startdate = '2014-01-01 00:00:00' set @enddate = '2015-12-31 23:59:59' select x.* [dflt].[riskfactorsx] (select c.id, ip.admitdatetime datetime, ip.sta3n, ip.icd9code, ip.icd9description, 'ip' ds dbo.inpat_inpatient ip inner join dbo.cohort c on (c.id = ip.id) ip.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = @riskfactortype) , ip.admitdatetime between @startdate , @enddate union select c.id, ov.visitdatetime datetime, ov.sta3n, ov.icd9code, ov.icd9description, 'op' ds dbo.outpat_visit ov inner join dbo.cohort c on (c.id = ov.id) ov.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = @riskfactortype) , ov.visitdatetime between @startdate , @enddate ) x order id, datetime, ds go declare @riskfactors typeriskfactorstable insert @riskfactors(riskfactortype,riskfactoricd9) values ('tobacco', '305.1'), ('tobacco', '989.84'), ('tobacco', 'v15.82'), ('pvd', '443.9'), ('ptsd', '309.81'), ('hypotension', '458.0'), ('hypotension', '458.1'), ('hypotension', '458.2'), exec dflt.getriskfactors @riskfactortype = 'tobacco' to make stored procedure getriskfactors work riskfactors need pass created table name procedure like
select x.* @table instead of
select x.* [dflt].[riskfactorsx] how accomplish this.
the type of @riskfactortype passed stored procedure typeriskfactorstable. think want varchar instead.
create procedure dflt.getriskfactors @riskfactoricd9 typeriskfactorstable readonly, @riskfactortype varchar(30) declare @startdate datetime declare @enddate datetime set @startdate = '2014-01-01 00:00:00' set @enddate = '2015-12-31 23:59:59' select x.* [dflt].[riskfactorsx] (select c.id, ip.admitdatetime datetime, ip.sta3n, ip.icd9code, ip.icd9description, 'ip' ds dbo.inpat_inpatient ip inner join dbo.cohort c on (c.id = ip.id) ip.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = @riskfactortype) , ip.admitdatetime between @startdate , @enddate union select c.id, ov.visitdatetime datetime, ov.sta3n, ov.icd9code, ov.icd9description, 'op' ds dbo.outpat_visit ov inner join dbo.cohort c on (c.id = ov.id) ov.icd9code in (select riskfactoricd9 @riskfactoricd9 riskfactortype = @riskfactortype) , ov.visitdatetime between @startdate , @enddate ) x order id, datetime, ds go 
Comments
Post a Comment