quote:

---

Originally posted by Arnault

Hello,

Thank you for your quick answer ! Because I have to declare the temporary table with all the column parameters again and I want to keep all the declaration part in the function. Here you have to:

DECLARE #temp TABLE (col1, col2,..., coln)
select * into #temp from my_function

Because if I change the number of column in the function I have to do the same in the procedure!

---

quote:

---

Originally posted by Arnault

Sorry, I didn't read my sentence twice ! Declare is for table variable not temporary table ! But if I use the temp table or the table variable, my problem stays the same. I can't assign a table to another table without declaring it or creating it ! Is there a solution ?

Thx

Arnault

---

quote:

---

Originally posted by Lamprey

I don't understand what the OP wants or is trying to do either, but there are also Table-Valued Functions (TVP). That might fit the bill. Which is what I think Brett is trying to demonstrate.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Lamprey

I don't understand what the OP wants or is trying to do either, but there are also Table-Valued Functions (TVP). That might fit the bill. Which is what I think Brett is trying to demonstrate.

---

If you see his first thats what he's already having. I think attempt is to store returned resultset somewhere
see below words

I have a function that return a table and I would like to use this table in a procedure at different places.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Arnault

The problem with table valued parameter is that you can't return such a table in an udf. I think I will have to declaree my table columns twice (in the function and in my procedure).

Arnault

---

CREATE FUNCTION [dbo].[CHUV_F_Census] 
(
		--  Parameters for the function 
	
		@HSF_SESSION_ENTITYID		varchar(20) = null,
        @HSF_SESSION_USEROID		varchar(20) = null,
		@pvchEntityName				varchar(2000) = null,			        
        @pchCensusType				char(1) = null   
       
		
       
)	
	
RETURNS @tblCensus TABLE 
(
	PatientOID					int,	
    VIPIndicator				bit,		
	LikeIP						tinyint,
	Initial						varchar(64),	
	GenerationQualifier			varchar(30),
	FirstName					varchar (30),
	MiddleName					varchar (30),
	LastName					varchar (60),		
	PtLastNameGenQlr			varchar(90),
	Age							varchar(10),	
	Sex							varchar(8),			
	LOS							int,
	BirthDate					datetime,	
	EntityName					varchar (75),
	EntityOID					int,		
	VisitOID					int,			
	VisitType					varchar (30),
	VisitStatus					tinyint,	
	VisitStartDateTime			datetime,	
	VisitEndDateTime			datetime,
	UnitContactedOID			int,	
	PatientLocationName			varchar(75),
	PatientLocationOID			int,
	IsolationIndicator			varchar(64),				
	LatestBedName				varchar (75),
	RelationType				tinyint,	
	StaffOID					int,			
	PurgeDate					datetime,
	EntitySeqNo					int,	
	UnitSeqNo					int,			
	BedSeqNo					int,
	PatientStatusCode			varchar(255),				
	PublicityIndicator			varchar(64),
	DeathIndicator				bit,	
	AlternateVisitID			varchar (75),	
	MRUAbbreviation				varchar (10),
	PatientAccountID			varchar (20),				
	MPINumber					varchar (20),
	ExternalPatientID			varchar (20),				
	EntityAbb					varchar (10),
	CnsStaffOID					int,	
	CnsStfLastNameGenQlr		varchar(90),
	CnsStfTitle					varchar(64),
	CnsStfLastname				varchar (60),	
	CnsStfMiddleName			varchar (30),
	CnsStfFirstName				varchar(30),				
	CnsStfGenQlfr				varchar (30),
	CnsDnrStaffOID				int,	
	CnsDnrLastnameGenQlfr		varchar(90),
	CnsDnrTitle					varchar(64),
	CnsDnrLastname				varchar (60),	
	CnsDnrMiddleName			varchar (30),
	CnsDnrFirstName				varchar(30),				
	CnsDnrGenQlfr				varchar (30),
	CnsPtType					varchar (20),
	ChiefCompl					varchar(255),	
	Enterprise					varchar(184) 		  
)

AS BEGIN

Some code here


RETURN;
END;

ALTER PROCEDURE [dbo].[dbo.census]
        @HSF_SESSION_ENTITYID		varchar(20) = null,
        @HSF_SESSION_USEROID		varchar(20) = null,
		@pvchStaff					varchar(4000) = null,
		@pvchEntityName				varchar(2000) = null,		
		@pvchStaffSearchStr 		varchar(50) = null,			        
        @pchCensusType				char(1) = null,   
        @pchCensusSort				char(1) = null
		
AS

SET NOCOUNT ON
BEGIN

-- Liste des entités (périmètre de droits) de l'utilisateur 

DECLARE @tblMedecin1 TABLE (
	patientaccountID			int,
	[Name]						varchar(100)
)

DECLARE @tblMedecin2 TABLE (
	patientaccountID			int,
	[Name]						varchar(100)
)

DECLARE @tblMedecin3 TABLE (
	patientaccountID			int,
	[Name]						varchar(100)
)

DECLARE @tblInfirmier table (
	patientaccountID			int,
	[Name]						varchar(100)
)

DECLARE @tblAdditionalObservations table (
	Patient_oid					int,
	FindingAbbr					varchar(32),
	[Value]						varchar(4000)
)

DECLARE @tblProblems table (
	Patient_oid					int,
	[Value]						varchar(2048)
)

DECLARE @tblDiags TABLE (
	patient_oid					int,
	diag_principal				varchar(4000),
	diag_secondaire				varchar(4000),
    SortOrder					int
)


DECLARE @tblCensus TABLE 
(
	PatientOID					int,	
    VIPIndicator				bit,		
	LikeIP						tinyint,
	Initial						varchar(64),	
	GenerationQualifier			varchar(30),
	FirstName					varchar (30),
	MiddleName					varchar (30),
	LastName					varchar (60),		
	PtLastNameGenQlr			varchar(90),
	Age							varchar(10),	
	Sex							varchar(8),			
	LOS							int,
	BirthDate					datetime,	
	EntityName					varchar (75),
	EntityOID					int,		
	VisitOID					int,			
	VisitType					varchar (30),
	VisitStatus					tinyint,	
	VisitStartDateTime			datetime,	
	VisitEndDateTime			datetime,
	UnitContactedOID			int,	
	PatientLocationName			varchar(75),
	PatientLocationOID			int,
	IsolationIndicator			varchar(64),				
	LatestBedName				varchar (75),
	RelationType				tinyint,	
	StaffOID					int,			
	PurgeDate					datetime,
	EntitySeqNo					int,	
	UnitSeqNo					int,			
	BedSeqNo					int,
	PatientStatusCode			varchar(255),				
	PublicityIndicator			varchar(64),
	DeathIndicator				bit,	
	AlternateVisitID			varchar (75),	
	MRUAbbreviation				varchar (10),
	PatientAccountID			varchar (20),				
	MPINumber					varchar (20),
	ExternalPatientID			varchar (20),				
	EntityAbb					varchar (10),
	CnsStaffOID					int,	
	CnsStfLastNameGenQlr		varchar(90),
	CnsStfTitle					varchar(64),
	CnsStfLastname				varchar (60),	
	CnsStfMiddleName			varchar (30),
	CnsStfFirstName				varchar(30),				
	CnsStfGenQlfr				varchar (30),
	CnsDnrStaffOID				int,	
	CnsDnrLastnameGenQlfr		varchar(90),
	CnsDnrTitle					varchar(64),
	CnsDnrLastname				varchar (60),	
	CnsDnrMiddleName			varchar (30),
	CnsDnrFirstName				varchar(30),				
	CnsDnrGenQlfr				varchar (30),
	CnsPtType					varchar (20),
	ChiefCompl					varchar(255),	
	Enterprise					varchar(184) 		  
)

DECLARE 
	@iEntityOID            		int,
	
	@vchStaffLastname			varchar(30),
	@vchStaffFirstname			varchar(30),
	@iDelimiterindex  			int,
	@vchSearchString			varchar(60),		-- Décomposition du nom pour recherche
    @nCurrPatOID1				int, 
    @prevPatOID1				int,
    @sDiags						varchar(4096),		-- Diag. structurés
    @sRankValue					varchar(1024), 
    @sRankValue1				varchar(1024), 
    @sDisplayName				varchar(1024), 
    @cSeparateur				char(3), 
    @sValue						varchar(1024), 
    @sDispValue					varchar(1024), 
    @Ordre						int
    

INSERT INTO @tblCensus SELECT * FROM dbo.CHUV_F_Census(
		@HSF_SESSION_ENTITYID,
        @HSF_SESSION_USEROID,
		@pvchEntityName,			        
        @pchCensusType)
        
-- Mise à jour du motif de prise en charge
update t1 set t1.ChiefCompl=t2.PatientReasonForSeekingHC
from @tblCensus t1,HPatientVisit t2 WITH (NOLOCK) where t1.VisitOID=t2.ObjectId

-- Mise à jour de l'unité de prise en charge
update t1 set t1.Enterprise=t2.HealthCareUnitName
From @tblCensus t1,HHealthCareUnit t2 WITH (NOLOCK) where t2.OrganizationType=1

-- Mise à jour des acteurs de la prise en charge -- médecins
insert into @tblMedecin1
	select  
		t1.patientaccountID,
		t11.[Name]
	from 
		HCensusPatientList t1, 
		HStaff  t11
	where 
		t1.patientaccountID in (select distinct patientaccountID from @tblCensus)
	and 
		t1.relationType = 2
	and 
		t1.Staffoid = t11.objectid
	and 
		t11.stafftype = 0

insert into @tblMedecin2
	select  
		t1.patientaccountID,
		t11.[Name]
	from 
		HCensusPatientList t1, 
		HStaff  t11
	where 
		t1.patientaccountID in (select distinct patientaccountID from @tblCensus)
	and 
		t1.relationType = 0
	and 
		t1.Staffoid = t11.objectid
	and 
		t11.stafftype = 0

insert into @tblMedecin3
	select  
		t1.patientaccountID,
		t11.[Name]
	from 
		HCensusPatientList t1, 
		HStaff  t11
	where 
		t1.patientaccountID in (select distinct patientaccountID from @tblCensus)
	and 
		t1.relationType = 4
	and 
		t1.Staffoid = t11.objectid
	and 
		t11.stafftype = 0

-- Mise à jour des acteurs de la prise en charge -- soignants
insert into @tblInfirmier
	select   
		t2.patientaccountID,
		t12.[Name]
	from 
		HCensusPatientList t2, 
		HStaff  t12
	where 
		t2.patientaccountID in (select distinct patientaccountID from @tblCensus)
	and 
		t2.relationType = 14
	and 
		t2.Staffoid = t12.objectid
	and 
		t12.stafftype = 2

END

-- Mise à jour du motif de prise en charge
	UPDATE t1 
	   SET ChiefCompl = t2.PatientReasonForSeekingHC
      FROM dbo.CHUV_F_Census(
				@HSF_SESSION_ENTITYID,
				@HSF_SESSION_USEROID,
				@pvchEntityName,			        
				@pchCensusType)
INNER JOIN HPatientVisit t2 /* (WITH NOLOCK)*/ 
		ON t1.VisitOID=t2.ObjectId