Use of User Defined Table Types

1

goal : create a stored procedure that loads a list of strings into a table variable.
I have user defined Table Type as

CREATE TYPE dbo.StringList
AS TABLE
(
item varchar(100)
);
GO

and a stored procedure

USE [theDB]
GO
/****** Object: StoredProcedure [dbo].[GenericStringList] Script Date: 3/14/2017 10:24:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GenericStringList]
@List AS dbo.StringList READONLY
AS
BEGIN
SET NOCOUNT ON;

SELECT item FROM @List;
END

I understand I would create a datatable to pass into it from c#, but I'd like to test it from the execute stored procedure from SQL Server Management Studio. What I see is
Parameter @List
DataType StringList
?? What goes in Value to test it ??

Thanks !

2 
DECLARE @MyStringList
AS dbo.StringList
  
INSERT INTO @MyStringList(item)
VALUES('1,2,3')
     ,('4,5,6,7')
     ,('8,9')

EXEC dbo.GenericStringList @MyStringList
1 Like
3

Thanks - your sample does work for test, I was actually trying to use the right click view which generates this DECLARE @return_value int

EXEC @return_value = [dbo].[GenericStringList]
@List = ''foo','moo''

SELECT 'Return Value' = @return_value

GO

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'foo'

Test StrijngList SP.jpg713×123 28 KB

Is it possible ???

4

Not using

@List = ''foo','moo''

but it is possible that there is another "text based" way of populating a table-parameters, but if there is I'm not aware of it.

Suggest you do your tests using pure-SQL (e.g. starting from the example I gave and just EXECUTE that in SSMS / similar, and hopefully develop your [GenericStringList] procedure from there :slight_smile:

5

thanks !