CODEWITHSUNDEEP
sql-server-2008
Wissam Abbas
Wissam Abbas

Reputation: 1

variable that can hold a multiple values in sql

i am facing a slite problem in sql server 2008 and here is the situation 

IF @PatCntFamNbr is not null 
BEGIN

select  t.Cntid AS Center , t.PatFName AS FirstName   , t.PatMName AS MiddleName , t.PatLName AS     LastName  
from tblpatient t INNER JOIN  TblPatientCentres p
 ON   p.PatID_fk = t.PatId_PK
where   p.CentreID_fk=@cntid  and @patid in (select patid_fk from tblpatientcentres where cntfammbnbr=@cntfammbnbr)
END

my major problem is that this must show lots of names that belongs to the same family and the variable @patis is accepting only one variable and this is not allowing the procedure to work properly

i will be really thankful for any help

Upvotes: 0

Views: 28

Answers (1)

James S
James S

Reputation: 3588

If you need a variable that can hold multiple values then you should use a Table Variable
IE something like DECLARE @patid TABLE(ID INT)

If you need the variable to be passed in via a client application then research table valued types

However your query doesnt look like it needs either - it just needs fixing...

IF @PatCntFamNbr is not null 
BEGIN

select  t.Cntid AS Center , t.PatFName AS FirstName   , t.PatMName AS MiddleName , t.PatLName AS     LastName  
from tblpatient t INNER JOIN  TblPatientCentres p
 ON   p.PatID_fk = t.PatId_PK
where   p.CentreID_fk=@cntid  and p.cntfammbnbr=@cntfammbnbr -- this is all you need
END

Upvotes: 1

Related Questions