3

I have TBV function . Like this

ALTER FUNCTION [dbo].[fn_Functiont]
(
 @accessibleIds   ListAccesableIds READONLY
)
RETURNS TABLE 
AS
RETURN 
(   
SELECT d.*, b.Name AS Name, ps.Name AS PaymentSystemName, c.UserName AS UserName, c.FirstName AS ClientFirstName, c.LastName AS LastName, c.Number AS DocumentNumber, c.Id
FROM Document AS d
JOIN System AS ps ON d.SystemId = ps.Id
JOIN Client AS c ON c.Id = d.ClientId
LEFT JOIN Shop AS b ON b.Id = d.ShopId
WHERE d.OperationTypeId IN (2, 4, 5) AND c.Type = 1
)

I want to pass to this function List containing integers.I was bale to create table valued type but here is the question how could i pass List input parameter to the function?I know that i could pass lists via ADO.Net to the SP very easly but my problem regards to the Entity Framework and TBF functions

I am calling this function through Entity Framework 6 the approach which we used was database first.

6

On SQL-Server 2014 you can take advantatge of TYPE (alias data type), and pass it as a parameter of an Inline UDF simulating an array of integer, or any other tabular data.

Keep in mind you must use READONLY.

IF OBJECT_ID('dbo.MyTest') IS NOT NULL
    DROP TABLE dbo.MyTest;
GO

IF OBJECT_ID('dbo.MyFunction') IS NOT NULL
    DROP FUNCTION dbo.MyFunction;
GO

IF TYPE_ID ('dbo.Mytype') IS NOT NULL
    DROP TYPE dbo.Mytype;
GO

CREATE TYPE dbo.Mytype AS TABLE (Id int);
GO

CREATE TABLE dbo.MyTest (Id int, Val char(3));
INSERT INTO dbo.MyTest VALUES (1,'V1'),(2,'V2'),(10,'V10'),(11,'V11');
GO

CREATE FUNCTION dbo.MyFunction(@Table MyType READONLY)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM   MyTest
    WHERE  Id IN (SELECT Id FROM @Table)
);
GO
DECLARE @myTable Mytype;
INSERT INTO @myTable(Id) VALUES(1),(2),(3),(4),(5);

SELECT * FROM MyFunction(@myTable);

| Id | Status |
|:--:|:------:|
|  1 |   V1   |
|  2 |   V2   |

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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