0

If you have two table Employee and Department.

  • tblEmp - EmpID, EmpName,DeptID are the fields
  • tblDepartment - DeptID,DeptName are the fields

Employee and Department table have the foreign key relation.

SELECT * FROM tblEmp e
INNER JOIN tblDepartment d on d.DeptID = e.DeptID
WHERE d.DeptID IN ('1','2')

How to write stored procedure for above statement? The department id values in where clause IN statement are dynamic (1,2) or (1,2,3) what ever I will pass them dynamically.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
krishna
  • 23
  • 3
  • 9
  • I'm not aware of any way to do that other than passing the multiple values in as a single delimited string and then breaking it up in the SQL code. – jmcilhinney Sep 07 '15 at 11:50
  • 1
    @jmcilhinney - There are other options, but they're all *(imo)* quite clunky too. For example, table valued parameters? – MatBailie Sep 07 '15 at 12:08

2 Answers2

4

You can pass all values in single varchar(max) object.

Then in your sp, you can split your values by using split function and then put 'IN' clause on it.

Create procedure sp_test
@var1 nvarchar(max)
as
begin
    select * from tblEmp e
    Inner join tblDepartment d on d.DeptID = e.DeptID
    where d.DeptID IN select value from dbo.split(@var1))
end
Mike Clark
  • 1,860
  • 14
  • 21
1

create a function to split the comma separated values as below,

CREATE FUNCTION [dbo].[FnSplit]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
Id int identity(1,1),
Value nvarchar(100)
) 
AS  
BEGIN
   While (Charindex(@SplitOn,@List)>0)
   Begin 
       Insert Into @RtnValue (value)
       Select
       Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
       Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
   End 

   Insert Into @RtnValue (Value)
   Select Value = ltrim(rtrim(@List))
   Return
END

In your Stored Procedure call the function as below,

CREATE PROCEDURE usp_getvalues
@var1 nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM tblEmp e
    INNER JOIN tblDepartment d 
    on d.DeptID = e.DeptID
    WHERE d.DeptID IN (SELECT value FROM [dbo].[FnSplit](@var1))

    SET NOCOUNT OFF;
END