SQL datatype for using a string sentence

Question

I want to create a SQL Server stored procedure like this:

CREATE PROCEDURE dbo.uspGetCharacterID                             
   (@characterName vchar(1000)) 
as            
   SELECT c.charatcer_full    
   FROM CHARACTER c (nolock)      
   WHERE character_full IN (@characterName)      
   ORDER BY C.characterID

From code, @charactername I am passing --> 'Batman in latest movies', 'Superman in latest movies'

But in code its returning zero rows.

NOTE: if I run the same select query in SQL with those string, it successfully returns two rows.

QUESTION: which datatype should be used, so that requirement is satisfied?

'Varchar' and 'Text' didn't work.

Please guide

Thanks

Will this string represent data in single record Batman in latest movies, Superman in latest movies — ejb_guy
– ejb_guy, Commented Jun 1, 2012 at 6:21
The tabele 'Character', 'Batman in latest movies' has 1 row and 'Superman in latest movies' has 1 row. So running the above query returns two rows. — Kings
– Kings, Commented Jun 1, 2012 at 6:25
It's not a question of the datatype - the point is, you cannot do this they way you're doing it. You cannot pass in a list of strings as a varchar and then used those in a .. IN (....) construct. Doesn't work that way. See this other SO question that deals with this issue, and see the answer provided by KM. - that's the way to do it — marc_s
– marc_s, Commented Jun 1, 2012 at 6:37
@marc_s -- So how would I pass more than one string in 'IN' clause using Stored Proc ? And why the Select query mentioned above, runs succesfully when run manually with those list of strings ? — Kings
– Kings, Commented Jun 1, 2012 at 6:42
@Kings. see that other SO question I linked to - the answer provided there shows you how to do this! — marc_s
– marc_s, Commented Jun 1, 2012 at 6:44

Quassnoi · Accepted Answer · 2012-06-01 06:45:06Z

1

CREATE TYPE tp_names AS
        TABLE
        (
        name VARCHAR(1000) NOT NULL
        )

GO

CREATE PROCEDURE
        uspGetCharacterID (@names tp_names READONLY)
AS
        SELECT  c.character_full    
        FROM    CHARACTER c
        WHERE   character_full IN
                (
                SELECT  name
                FROM    @names
                )
        ORDER BY
                characterID

GO

DECLARE @names tp_names

INSERT
INTO    @names
VALUES
        ('Batman in latest movies'),
        ('Superman in latest movies')

EXEC    uspGetCharacterID
                @names = @names

answered Jun 1, 2012 at 6:45

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

Collectives™ on Stack Overflow

SQL datatype for using a string sentence

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related