1

Possible Duplicate:
Parameterizing an SQL IN clause?

Hi how can we send multiple uniqueidentifiers to a stored procedure.

Ex:

Create procedure showall
(@empids uniqueidentifier(50) )
as
select * from emp where empid in (@empid)

like this?

Community
  • 1
  • 1
jestges
  • 3,686
  • 24
  • 59
  • 95
  • With a split function or table valued param, lots of dups; http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause – Alex K. Mar 30 '12 at 13:15
  • I want to pass multiple uniqueidentifier types not strings. Is it possible? – jestges Mar 30 '12 at 13:25
  • Yes, correctly formatted guids in strings are interchangeable with uniqueidentifier – Alex K. Mar 30 '12 at 13:26

2 Answers2

2

As Diego mentioned already, you can use a table valued parameter. Here is an example of how you could use a table valued parameter to inner join against your passed list, thus filtering your list accordingly.

    create procedure showall
    (
     @empids uniqueidentifier(50)
    ,@myTableVariable mySchema.myTableType readonly
    )
as 
    select
        *
    from
        emp e
        inner join @myTableVariable m
        on e.empids = m.empids

    --where
    --  empid in ( @empid )

If you never have used a table valued parameter before it can be a little confusing. You need to create a user defined table type. As the section on the creation shows, once you've created the table structure you could go back and then define your parameter type as a table value parameter. 

create type mySchema.myTableName as table
( empids uniqueidentifier)

Then you reference it in the parameter clause as above in the first code sample. Hope this helps, it confused me the first time I used it, but it sure can be handy and reduce trips from an application.

sheldonhull
  • 1,807
  • 2
  • 26
  • 44
0

multiple parameters can be passed as a comma (or any other symbol) separated string and then treated inside the proc or as a table-valued parameter

Diego
  • 34,802
  • 21
  • 91
  • 134