asked
table value parameters with stored procedure
Hi I have a stored procedure that when its executed it outputs Persons name, date created their id, location, description etc.... I have two parameters that the SP uses, @Ent which is Location and @date. What I am trying to achieve is have a job execute the stored procedure and make it use table valued parameters so that the SP is executed for all the @Ent variable. For example the stored procedure looks like this... EXEC [dbo].[storedProcName] 711, '01/23/2012' select vwo.UpperOrgDesc, vwo.LowerOrgDesc as Location, epsorg.OrgEntSys as LocationSys ,vwo.Name4, ent.DisplayName, ep.EpisodeSys, ep.ResidentSys, ep.AdmissionID, ep.EpisodeType, ep.AdmissionDate, convert (varchar,convert(smalldatetime,ep.DateCreated),101)as DateCreated from T.Episodes as ep inner join T..Entities ent on ent.EntitySys = ep.ResidentSys inner join T.EpsOrganizations epsorg on epsorg.EpisodeSys = ep.EpisodeSys and OrgLevel >=60 inner join T.vwOrganizations vwo on epsorg.OrgEntSys = vwo.OrgEntSys and vwo.OrgSet = 1 where ep.DateCreated > @date and ep.DateCreated < @date +1 and ep.AdmissionDate <= ep.DateCreated and EpisodeStatus in ('AD') and EpisodeType in ('AD') and DischargeDate is null and CareSetting in ('RL','AD') and vwo.Ent4 = @Ent) order by ep.DateCreated THANKS IN ADVANCE
Instead of trying to execute the query for each value, how about just running the query and then using the table variable to JOIN it to the data. That will work. But, performance might be junk because the table variables do not have statistics.
14 People are following this question.
