0

How to pass multiple values to a stored procedure in SQL Server 2008?

My table looks like this:

    id  Tag                platform
    1  #class1,#class2      CS
    2  #class1              PS
    3  #class2              CS

Stored procedure:

 ALTER PROCEDURE [dbo].[usp_Get]-- 1,"'#class1,#class2'"
    @Appid INT,
    @TagList NVARCHAR (MAX)
    AS

    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
     SET NOCOUNT ON;

      declare @tags varchar(MAX)
        set @tags = @TagList
        create table #t (tag varchar(MAX))
        set @tags = 'insert #t select ' + replace(@tags, ',', ' union select ')
        exec(@tags)
    Select 
        id FROM dbo.List WHERE ((appid=@Appid)) AND ((Tags IN(select tag from #t)
    END

If I query 

[dbo].[usp_Get] 1,"'#class1'"

I'm getting only the second row. But first row also has #class1...

Please tell me ........

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1237131
  • 1,853
  • 3
  • 26
  • 35

1 Answers1

0

I'd suggest you using table valued parameters.

This question deals with sending value from .NET environment. 

CREATE TYPE VarcharContainer as TABLE (
    [Value] NVARCHAR(MAX)
)

CREATE PROCEDURE [dbo].[usp_Get] (
    @Appid INT,
    @TagList VarcharContainer READONLY
) AS 
BEGIN
    SELECT id FROM dbo.List WHERE appid=@Appid AND Tags IN (select [Value] from @TagList)
END

This implies that there is only one tag in the column. I strongly recommend you to avoid denormalization and multivalued fields.

EDIT

More information on table valued parameters you can find on MSDN

----------------
|   TableName  |
----------------
|   id         |
|   --         |
|   platform   |
----------------

-------------------
|   TableNameTag  |
-------------------
|   TableNameId * |
|   -----------   |
|   TagId *       |
|   -----         |
-------------------

----------------
|   Tag        |
----------------
|   TagName    |
|   TagId      |
|   -----      |
----------------

This is a suggested schema. This conforms to the basic rules of proper relation

1.Entries in columns are atomic (or single-valued)

EDIT

Just to remind about badness of the denormalization, here is one of my favorite quotes

Denormalization is almost always a bad idea - don't do it unless you really know what you are doing. Or you'll start asking questions like: this, this, this, this, ...

source

Community
  • 1
  • 1
Oybek
  • 7,016
  • 5
  • 29
  • 49
  • @TagList VarcharContainer READONLY showing invalid datatype error. – user1237131 Mar 31 '12 at 07:11
  • @user1237131 Your question title asks how to pass multiple values into a SP. The answer is table valued types. As far as the data in your table is concerned, I recommend you to normalize it as shown in the ERD. This is the most straightforward way to achieve what you want. You'll end up with two more tables but this will save you a lot of hours of debugging and nerves. Of course if you have a permission to change the db schema. – Oybek Mar 31 '12 at 07:41