x

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

more ▼

asked Apr 10, 2013 at 06:28 PM in Default

avatar image

tombiernacki
338 20 22 27

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 11, 2013 at 06:32 PM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x93
x19

asked: Apr 10, 2013 at 06:28 PM

Seen: 1091 times

Last Updated: Apr 11, 2013 at 06:32 PM

Copyright 2017 Redgate Software. Privacy Policy