Creating a table valued function that requires several parameters

Question

I need some help with my t-sql code. Background I have a table that BY YEAR data output by columns. So a single row will have CN[Year 0]........CN[Year 10], Premiums[Year 0]......[Year 10] etc etc. I am trying to write a function that takes an integer parameter and then returns a table only for the specified year columns i.e CN, Premium etc etc. Ultimately I'd like to concatenate the tables at some point.I have the basics to run the code manually but unsure how to automate my function so it can return the table.

At the moment I can run the code within ------from here ------to here manually but not automate the code so it can return a table

CREATE FUNCTION [xxxxxxx_Price_Schema].get_MultiYear(@year varchar(1))
RETURNS TABLE
AS
RETURN
------from here 
declare @year varchar(1);
declare @yearNo varchar(1);
declare @customerNo varchar(25);
declare @CN varchar(3);
declare @SQLString NVARCHAR(max);
declare @SQLResult nvarchar(max);

set @year = 1
set @yearNo = cast(@year as varchar(1))
set @customerNo = QUOTENAME(cast('CustomerNumber[Year '+ @yearNo + ']' as varchar(25)))
set @CN = concat('CN',@yearNo)

set @SQLString = 
            '
            select distinct
            [CN[Year '+ @yearNo +']]] AS CN
            ,[Premium[Year '+ @yearNo + ']]] AS Premium
            ,[Age[Year '+ @yearNo + ']]] AS Age
            ,[Sex[Year '+ @yearNo + ']]] AS Sex
            FROM [Test].[Test202002].[PHL_MultiYear_Test_Output] 
              WHERE ' + @customerNo + ' = '''+ @CN + ''' 
             '

EXECUTE sp_executesql @SQLString
----too here

RETURN @SQLString

END
GO

DESIRED RESULT IF MY input param is 0

@JohnCappelletti do you have any suggestions for a work around ? — project_kingz
– project_kingz, Commented May 15, 2019 at 14:03
Please provide a set of sample data (best provided as DDL and DML - read about minimal reproducible example) and the expected output... This might be a case of the xy-problem. There might be a better approach... — Gottfried Lesigang
– Gottfried Lesigang, Commented May 15, 2019 at 14:14
If you need dynamic sql, then you would have to migrate into a stored procedure — John Cappelletti
– John Cappelletti, Commented May 15, 2019 at 14:14
@Shnugo data I have is business sensitive so best I can do is as already done describe my table structure and required out. A mock table will be no more descriptive than stated above. — project_kingz
– project_kingz, Commented May 15, 2019 at 14:21

John Cappelletti · Accepted Answer · 2019-05-15 17:56:37Z

2

You will not be able to get dynamic column names without Dynamic SQL. However, if you don't mind standardizing the results, consider UNPIVOTing your data

This can easily be incorporated into a Table-Valued Function.

Example dbFiddle

Declare @Year varchar(1) = '0'

Select SelYear = @Year
      ,CN      = max(case when Item like 'CN%'      then Value end)
      ,Premium = max(case when Item like 'Premium%' then Value end)
 From  (
         Select *
          From  (Select * 
                       ,RN=Row_NUmber() over (Order by (Select null)) 
                 From  YourTable 
                 Where 1=1 -->>> Put your actual WHERE here <<<--
                ) src
          Unpivot ( Value for Item in ([CN [Year 0]]],[CN [Year 1]]],[Premium [Year 0]]],[Premium [Year 1]]] )) unp  -->>> Add 2 through 5 <<<--
          Where Item like '%'+@Year+']'
       ) A
 Group By RN

Returns

SelYear CN  Premium
0       0   10
0       0   20
0       0   30
0       0   40

Updated for odd column names dbFiddle

EDIT #2 - Variable Datatypes

Select SelYear = @Year
      ,CN      = max(case when Item like 'CN%'      then Value end)
      ,Premium = max(case when Item like 'Premium%' then Value end)
 From  (
     Select *
      From  (Select * 
                   ,RN=Row_NUmber() over (Order by (Select null)) 
         From  YourTable 
         Where 1=1 -->>> Put your actual WHERE here <<<--
        ) src
      Cross Apply (  values ('CN [Year 0]'     ,convert(varchar(50),[CN [Year 0]]]) )
                           ,('CN [Year 1]'     ,convert(varchar(50),[CN [Year 1]]]) )
                           ,('Premium [Year 0]',convert(varchar(50),[Premium [Year 0]]]) )
                           ,('Premium [Year 1]',convert(varchar(50),[Premium [Year 0]]]) )
                  )B(Item,Value)
      Where Item like '%'+@Year+']'
       ) A
 Group By RN

edited May 15, 2019 at 17:56

answered May 15, 2019 at 15:21

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

project_kingz Over a year ago

If my columns were in this format [CN [Year 0]] , [CN [Year 1]] etc etc how would you modify your code

project_kingz Over a year ago

spot on solution. Thank you . Another question - hopefully quick. Some of my columns have data stored in different format so I am getting.---> The type of column "Premium [Year 0]" conflicts with the type of other columns specified in the UNPIVOT list. Any work around

John Cappelletti Over a year ago

@project_kingz Different format? Do you mean different datatype?

project_kingz Over a year ago

Yes sorry I meant different data type. Some are varchar and others int etc

John Cappelletti Over a year ago

@project_kingz In the future, please supply an appropriate data sample. Rather than an UNPIVOT. we can unpivot the data via a CROSS APPLY. Please note that we are converting the columns into a varchar(50) , but you can decide on what is more appropriate. Here is a dbFiddle which shows the FINAL approach dbfiddle.uk/…

|

Collectives™ on Stack Overflow

Creating a table valued function that requires several parameters

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related