patternsqlMinor
Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)
Viewed 0 times
serverarraypassingusersqlfunctiondefinedinlineintegersparameter
Problem
I have TBV function . Like this
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.
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.
Solution
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.
dbfiddle here
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)
);
GODECLARE @myTable Mytype;
INSERT INTO @myTable(Id) VALUES(1),(2),(3),(4),(5);
SELECT * FROM MyFunction(@myTable);
| Id | Status |
|:--:|:------:|
| 1 | V1 |
| 2 | V2 |dbfiddle here
Code Snippets
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)
);
GODECLARE @myTable Mytype;
INSERT INTO @myTable(Id) VALUES(1),(2),(3),(4),(5);
SELECT * FROM MyFunction(@myTable);
| Id | Status |
|:--:|:------:|
| 1 | V1 |
| 2 | V2 |Context
StackExchange Database Administrators Q#197254, answer score: 6
Revisions (0)
No revisions yet.