CODEWITHSUNDEEP
sqlsql-serversqldatatypes
Kings
Kings

Reputation: 1591

SQL datatype for using a string sentence

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

Upvotes: 0

Views: 1678

Answers (1)

Quassnoi
Quassnoi

Reputation: 425361

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

Upvotes: 1

Related Questions