I have a stored procedure in SQL Server 2008 called 'GetPrices' with a Table-Valued Parameter called 'StoreIDs'.
This is the type i created for this TVP:
CREATE TYPE integer_list_tbltype AS TABLE (n int)
I would like to call the SP from my Entity Framework. But when I try to add the Stored Procedure to the EDM, i get the following error:
The function 'GetPrices' has a parameter 'StoreIDs' at parameter index 2 that has a data type 'table type' which is not supported. The function was excluded.
Is there any workaround this? Any thoughts?
Fabio
Since you can't use a table parameter, try passing in a CSV sting and have the stored procedure split it into rows for you.
There are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:
You need to create a split function. This is how a split function can be used:
SELECT
*
FROM YourTable y
INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value
I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.
For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers
that contains rows from 1 to 10,000:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
Once the Numbers table is set up, create this split function:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
( ----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO
You can now easily split a CSV string into a table and join on it or use it however you need:
CREATE PROCEDURE YourProcedure
(
@CSV_Param varchar(1000)
)
AS
--just an example of what you can do
UPDATE t
SET Col1=...
FROM dbo.FN_ListToTable(',',@CSV_Param) dt
INNER JOIN TBL_USERS t ON CAST(dt.value AS INT)=t.id
GO