how to pass Multipul integer values to a single parameter in a stored procedure

_{Thursday, December 29, 2011 7:55 PM}

hi, i have a simple table say employee table

i want to create a stored procedure taking department num as parameters that should take multiple values

select * from emp where deptno = @Deptno

how to pass multiple values to @Deptno of my procedure

can anybody explain with a simple example....

CREATE TABLE EMPSp (EMPNO NUMERIC(4), ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4),
                        HIRDATE DATETIME, SAL NUMERIC(7,2), COMM NUMERIC(7,2), DEPTNO NUMERIC(2,0))

select * from EmpSp       
               
INSERT INTO EmpSp VALUES(7369,'SMITH', 'CLERK',7902,'17-DEC-81',800,NULL,20)
INSERT INTO EmpSp VALUES(7499,'ALLEN', 'SALESMAN',7698,'20-FEB-81',1600,300,30)
INSERT INTO EmpSp VALUES(7521,'WARD', 'SALESMAN',7698,'22-FEB-81',1250,500,10)
INSERT INTO EmpSp VALUES(7566,'JONES', 'MANAGER',7739,'20-APR-81',2975,NULL,20)

this is my exaple table

Sam

_{Thursday, December 29, 2011 8:48 PM ✅Answered | 1 vote}

This recent thread  http://social.msdn.microsoft.com/Forums/en/transactsql/thread/edc7890a-6348-498d-8d78-81bbad5e0713 talks about the same concept. 

In SQL Server 2008 and up you can pass a table of department codes. In prior versions of SQL Server you can pass comma-delimited list (or XML) and split this list into a table.

Take a look at this quite long article explaining how to deal with this problem

Arrays & Lists in SQL ServerFor every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Thursday, December 29, 2011 9:18 PM ✅Answered}

Just wanted to add that using table-valued parameters if you are on SQL Server 2008 of higher is an excallend option. We have been using it extensively in our shop to replace our older stored procs that took a comma-delimited list

More information on using table-valued parameters can be found here : http://msdn.microsoft.com/en-us/library/bb510489.aspx

And a link to another excellent article on the same topic : http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168

If you are still on SQL 2005, the thread Naomi listed is an excellen discussion. However, you can definitely use this an excuse to upgrade your database to 2008 !

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

_{Thursday, December 29, 2011 9:43 PM ✅Answered}

Try to use split function like below :

FUNCTION SPLIT

CREATE Function [dbo].[SplitString]
(@List Varchar(Max), @Delimiter Char(1))
Returns @Items 
Table (Item Varchar(100))
As
Begin
Declare @Item Varchar(12), @Pos TinyInt
While Len(@List) > 0 
Begin
Set @Pos = CharIndex(@Delimiter, @List)
If @Pos = 0 Set @Pos = Len(@List) + 1 
Set @Item = Left(@List, @Pos - 1)
Insert @Items 
Select Ltrim(Rtrim(@Item))
Set @List = SubString(@List, @Pos + Len(@Delimiter), Len(@List))
End
Return
End
Go



alter proc GetEmployeesFromDept(@Deptno as varchar(MAX))
as
begin
Select * from EMPSp
where DEPTNO in
(select Item from [dbo].[SplitString](@deptno,','))
end
go



exec GetEmployeesfromdept '30,20'

 Best regards

_{Thursday, December 29, 2011 9:33 PM}

Hi Badii

here instead of taking Temp table i have used Department table where i have all my department no.s.... 

alter proc GetEmployeesFromDept(@Deptno as int)
as
begin
Select * from EMPSp

                  where DEPTNO in

                  (select distinct Deptno

                  from DEPT

                   where deptno= @deptno)
end
go

GetEmployeesfromdept 30,20

still i am getting error.....

Msg 8144, Level 16, State 2, Procedure GetEmployeesFromDept, Line 0
Procedure or function GetEmployeesFromDept has too many arguments specified.

Sam

_{Thursday, December 29, 2011 9:53 PM}

Thanks Baddi....

 just executed your code.... its working fine... have to go though your code...

thank you very much...

Sam

_{Thursday, December 29, 2011 11:20 PM}

Hi Badii...

In SSMS your code working fine...  when i started using your stores proc  in a simple report it is showing the following error when i pass multiple values to parameter...

but when i pass single value..  its working fine... 

can u tell the reason ?

Sam