Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I found this wonderful code here

    CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

    GO 
    CREATE PROCEDURE [dbo].[sp_UseStringList]
        @list StringList READONLY
    AS
    BEGIN
        -- Just return the items we passed in
        SELECT l.Item FROM @list l;
    END

    GO

but I dont know how to test it via SSMS please

declare @list   StringList  = '1,2,3,4,5'
exec sp_UseStringList @list

gives the error:

Operand type clash: varchar is incompatible with StringList

How can I test the list '1,2,3,4,5' please

share|improve this question
    
'1,2,3,4,5' is not a StringList...It is expecting a Table, as you've declared it CREATE TYPE [dbo].[StringList] AS TABLE –  Mark Sinkinson Sep 5 at 8:15
    
I still dont understand the jist of this please. I can create a table, insert the values, but how to execute please, this still gives error .declare ATlist as table(l nvarchar(max)) insert Into ATlist(l)values('1,2,3,4,5') exec sp_UseStringList ATlist –  Peter PitLock Sep 5 at 8:32
    
    
What exactly are you trying to do? What does "test the list" mean? Are you trying to turn the string '1,2,3,4,5' into a table as a list of integers, e.g. 1, 2, 3, 4, 5? –  Aaron Bertrand Sep 5 at 14:44
    
@AaronBertrand I couldn't quite work that out myself. I believe the question is something to do with passing a List<string> from C# to a SQL proc –  Mark Sinkinson Sep 5 at 15:01

1 Answer 1

up vote 2 down vote accepted

As you have declared dbo.StringList to be a User-Defined Table Type, you have to utilise it as you would a table.

So instead of assigning '1,2,3,4,5' as a string, you must INSERT this value into the table.

There's a pretty decent example in BOL, but I'll create one based on your example above.

CREATE TYPE [dbo].[StringList] AS TABLE(
  [Item] [NVARCHAR](MAX) NULL
);

GO 
CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

GO


DECLARE @StringList StringList

INSERT Into @StringList values('1,2,3,4,5') 

EXEC sp_UseStringList @StringList
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.