variable that can hold a multiple values in sql

Question

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

James S · Accepted Answer · 2014-12-22 16:54:56Z

1

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

answered Dec 22, 2014 at 16:54

James S

3,59818 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

variable that can hold a multiple values in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related