I needed to write function with table parameter.
an example:
CREATE FUNCTION getParentByBrandList
( @_BrandList TABLE(
BR_ID INT, BR_Name NVARCHAR(150), BR_ParentBrandID INT, BR_MasterBrandID INT, BR_Role INT,
BR_State INT, BR_OwnerID INT, BR_OwnerIP NVARCHAR(50), BR_CreateDate DATETIME, BR_UpdaterID INT,
BR_UpdaterIP NVARCHAR(50), BR_UpdateDate DATETIME
)
)
How can I do?
Thanx
Beginning from SQL Server 2008 you can use table valued parameters:
CREATE TYPE [dbo].[TableType] AS TABLE(
[ID] [INT] NULL
)
GO
CREATE FUNCTION fnTest
(
@t [dbo].[TABLETYPE] READONLY
)
RETURNS INT
AS
BEGIN
RETURN (SELECT TOP 1 ID FROM @t ORDER BY id DESC)
END
GO
DECLARE @t [dbo].[TABLETYPE]
INSERT INTO @t
VALUES ( 1 ),
( 2 )
SELECT dbo.fnTest(@t) AS ID
Output:
ID
2
Check this tutorial
Example : Passing Table Valued Parameter to a function
/* CREATE USER DEFINED TABLE TYPE */
CREATE TYPE StateMaster AS TABLE
(
StateCode VARCHAR(2),
StateDescp VARCHAR(250)
)
GO
/*CREATE FUNCTION WHICH TAKES TABLE AS A PARAMETER */
CREATE FUNCTION TableValuedParameterExample(@TmpTable StateMaster READONLY)
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE @StateDescp VARCHAR(250)
SELECT @StateDescp = StateDescp FROM @TmpTable
RETURN @StateDescp
END
GO
Try this...
CREATE FUNCTION getParentByBrandList ( )
RETURNS @_BrandList TABLE
(
BR_ID INT
,BR_Name NVARCHAR(150)
,BR_ParentBrandID INT
,BR_MasterBrandID INT
,BR_Role INT
,BR_State INT
,BR_OwnerID INT
,BR_OwnerIP NVARCHAR(50)
,BR_CreateDate DATETIME
,BR_UpdaterID INT
,BR_UpdaterIP NVARCHAR(50)
,BR_UpdateDate DATETIME
)
AS
BEGIN
--code to create/populate table
END;
You seem to be missing the returns, I have added the place-holder for the code. Also, since the name suggests Get By, you might be intensing to supply a parameter? if so, you just add to the parenthesis...
getParentByBrandList ( param)
This function is from AdevnrtureWorks2012 db from Microsoft which they provide for study purpose.
ALTER FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
[PersonID] int NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
[BusinessEntityType] [nvarchar](50) NULL
)
AS
-- Returns the first name, last name, job title and business entity type for the specified contact.
-- Since a contact can serve multiple roles, more than one row may be returned.
BEGIN
IF @PersonID IS NOT NULL
BEGIN
IF EXISTS(SELECT * FROM [HumanResources].[Employee] e
WHERE e.[BusinessEntityID] = @PersonID)
INSERT INTO @retContactInformation
SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
FROM [HumanResources].[Employee] AS e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @PersonID;
IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = v.[BusinessEntityID]
WHERE bec.[PersonID] = @PersonID)
INSERT INTO @retContactInformation
SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact'
FROM [Purchasing].[Vendor] AS v
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = v.[BusinessEntityID]
INNER JOIN [Person].ContactType ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
WHERE bec.[PersonID] = @PersonID;
IF EXISTS(SELECT * FROM [Sales].[Store] AS s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
WHERE bec.[PersonID] = @PersonID)
INSERT INTO @retContactInformation
SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact'
FROM [Sales].[Store] AS s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].ContactType ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
WHERE bec.[PersonID] = @PersonID;
IF EXISTS(SELECT * FROM [Person].[Person] AS p
INNER JOIN [Sales].[Customer] AS c
ON c.[PersonID] = p.[BusinessEntityID]
WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL)
INSERT INTO @retContactInformation
SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer'
FROM [Person].[Person] AS p
INNER JOIN [Sales].[Customer] AS c
ON c.[PersonID] = p.[BusinessEntityID]
WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL;
END
RETURN;
END;
