Declare and Valued Table Functions

Question

I need to put my Declare Section into a Table-valued function.

CREATE FUNCTION  [Manual_COUNTRIES_2019](
@risk_1, 
@risk_2,
@risk_3,
@risk_4,
@risk_5)
RETURNS (@risk_1, @risk_2,@risk_3,@risk_4,@risk_5) TABLE (Code VARCHAR(100))
AS
BEGIN

DECLARE @risk_1 TABLE (Code VARCHAR(100));
INSERT INTO @risk_1 (Code) VALUES ('AA'),('AB'),('AC');     
DECLARE @risk_2 TABLE (Code VARCHAR(100));
INSERT INTO @risk_2 (Code) VALUES ('AX'),('AY'),('AZ');
DECLARE @risk_3 TABLE (Code VARCHAR(100));
INSERT INTO @risk_3 (Code) VALUES ('BB'),('BC'),('BD'),('BE');
DECLARE @risk_4 TABLE (Code VARCHAR(100));
INSERT INTO @risk_4 (Code) VALUES ('DA'),('DB'),('DC');
DECLARE @risk_5 TABLE (Code VARCHAR(100));
INSERT INTO @risk_5 (Code) VALUES ('YY'),('XZ');
  RETURN
END

I took this as a template: Declare variable in table valued function

However, it is not happy with my table suggestion, nor with the list of @risks. What am I missing?

From [`CREATE FUNCTION`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15#syntax): "`RETURNS @return_variable TABLE `". — HABO, Sep 15 '20 at 13:23

Dale K · Accepted Answer · 2020-09-15T08:28:19.093

As you can't return multiple tables, I suggest adding another column which specifies the table you wanted to add it to, which can then be used to filter it on when you call it.

CREATE FUNCTION  [Manual_COUNTRIES_2019]()
RETURNS @Risk TABLE (RiskNum int, Code VARCHAR(100))
AS
BEGIN
    INSERT INTO @risk (RiskNum, Code)
        VALUES (1, 'AA'),(1, 'AB'),(1, 'AC'),
        (2, 'AX'),(2, 'AY'),(2, 'AZ'),
        (3, 'BB'),(3, 'BC'),(3, 'BD'),(3, 'BE'),
        (4, 'DA'),(4, 'DB'),(4, 'DC'),
        (5, 'YY'),(4, 'XZ');
    RETURN;
END;

However as you now don't need the variables I would suggest using an Inline Table Valued Function e.g.

CREATE FUNCTION [Manual_COUNTRIES_2019]()
RETURNS TABLE
RETURN
    SELECT *
    FROM (       
      VALUES (1, 'AA'),(1, 'AB'),(1, 'AC'),
      (2, 'AX'),(2, 'AY'),(2, 'AZ'),
      (3, 'BB'),(3, 'BC'),(3, 'BD'),(3, 'BE'),
      (4, 'DA'),(4, 'DB'),(4, 'DC'),
      (5, 'YY'),(4, 'XZ')
    ) AS X (RiskNum, Code);

You then pull the values you want as follows:

select Code from dbo.Manual_COUNTRIES_2019() where RiskNum = 1; -- For the first table, change the number to change the table.

looks good - I will need though to ask how to connect this now successfully — Anna, Sep 15 '20 at 07:47

Declare and Valued Table Functions

1 Answers1