How to convert a query to a a function

Question

I'm playing around with a query from a software partner that i did not create. I'm trying to use it as a function that i can insert into a table, since i'm no sql expert i'm having difficulties with the sintax.

Thanks to your suggestions i'm making progress, now i'm stuck on how to add an additional select to include some join tables, i have disabled that part converting it to a comment. This actually works without the final select but i need the final select, any ideas?

alter function Renetest

(
@companytest nvarchar(8),
@fiscalyeartest int
)

returns @PERIODBALANCE TABLE
    (
     [Company] NVARCHAR(8)
    ,[BookID] NVARCHAR(12)
    ,[BalanceAcct] NVARCHAR(200)
    ,[FiscalYear] INT
    ,[FiscalPeriod] INT
    ,[BalanceType] NVARCHAR(1)
        ,[SegValue1] NVARCHAR(50)
    ,[SegValue2] NVARCHAR(50)
       ,[FiscalYearSuffix] NVARCHAR(8)
    ,[FiscalCalendarID] NVARCHAR(12)
    )
    as
    begin


Declare @company nvarchar(8);
Declare @fiscalyear INT;
DECLARE @FiscalPeriod INT;
Set @company = 'epic03'
set @Fiscalyear =  '2013'
SET @FiscalPeriod=0;
DECLARE @MaxPeriod AS NVARCHAR(20);
SET @MaxPeriod=(
                SELECT
                    MAX([Erp].[GLBookPer].[FiscalPeriod])
                FROM
                    [Erp].[GLBookPer] WITH (NOLOCK)
                WHERE
                    [Erp].[GLBookPer].[Company] IN (@company)
                    AND [Erp].[GLBookPer].[FiscalYear]=@FiscalYear
               );


WHILE @FiscalPeriod<=(@MaxPeriod)
    BEGIN
        INSERT  INTO @PERIODBALANCE
                (
                 [Company]
                ,[BookID]
                ,[BalanceAcct]
                ,[FiscalYear]
                ,[FiscalPeriod]
                ,[BalanceType]
                ,[SegValue1]
                ,[SegValue2]
                ,[FiscalYearSuffix]
                ,[FiscalCalendarID]
                )
                SELECT
                    [Erp].[GLPeriodBal].[Company]
                   ,[Erp].[GLPeriodBal].[BookID]
                   ,[Erp].[GLPeriodBal].[BalanceAcct]
                   ,[Erp].[GLPeriodBal].[FiscalYear]
                   ,@FiscalPeriod AS [FiscalPeriod]
                   ,[Erp].[GLPeriodBal].[BalanceType]
                   ,[Erp].[GLPeriodBal].[SegValue1]
                   ,[Erp].[GLPeriodBal].[SegValue2]
                    ,[Erp].[GLPeriodBal].[FiscalYearSuffix]
                   ,[Erp].[GLPeriodBal].[FiscalCalendarID]
                FROM
                    [Erp].[GLPeriodBal] WITH (NOLOCK)
                WHERE
                    [Erp].[GLPeriodBal].[Company] IN (@company)
                    AND [Erp].[GLPeriodBal].[FiscalYear]=@FiscalYear
                    AND [Erp].[GLPeriodBal].[FiscalPeriod]<=@FiscalPeriod
                    AND [Erp].[GLPeriodBal].[BalanceType] IN ('D','B')

        SET @FiscalPeriod=@FiscalPeriod+1;  
        end;

/*
SELECT
    LTRIM(RTRIM([PERIODBALANCE].[Company])) AS [Company]
   ,LTRIM(RTRIM([PERIODBALANCE].[BookID])) AS [BookID]
     ,LTRIM(RTRIM(REPLACE([PERIODBALANCE].[BalanceAcct],'|','-'))) AS [BalanceAcct]
    ,LTRIM(RTRIM(ISNULL(NULLIF([PERIODBALANCE].[BalanceType],''),'--'))) AS [BalanceType]
     ,LTRIM(RTRIM(ISNULL(NULLIF([PERIODBALANCE].[FiscalYearSuffix],''),'--'))) AS [FiscalYearSuffix]
   ,LTRIM(RTRIM(ISNULL(NULLIF([PERIODBALANCE].[FiscalCalendarID],''),'--'))) AS [FiscalCalendarID]

FROM
    @PERIODBALANCE AS [PERIODBALANCE]*/

    return
    end

    go

Use *minimal* code necessary to ask your question. Even if this function were the problem, there is too much code because it is repetitive. What has your research & minimal code turned up just for your problem, "insert into a table"? — philipxy, Sep 17 '19 at 17:48
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. PS You have a syntax error. Read the grammar & manual. Show that constituent subexpressions are OK. — philipxy, Sep 17 '19 at 17:49
this query works ok, after i provide the parameters, "@company" and "@fiscalyear". I get the results on the SQL server datagrid and i want to store them on a table. How can i modify it in order to do that? — Rock Road, Sep 17 '19 at 18:18
I edited the query to be shorter. I hope it helps to understand it better — Rock Road, Sep 17 '19 at 20:28
How does this specific query have anything to do with what you are asking? Why do you need to do anything more than set a table to a any value in this code before you try to save it? Where are you stuck writing code or in some presentation of the basic thing you are trying to do? And again: 'What has your research & minimal code turned up just for your problem, "insert into a table"?' How is this not an easily found duplicate? — philipxy, Sep 18 '19 at 03:22
I have modified the query to be simpler. Again this is a query provided to me, that i did not write, i believe it was originally written as store procedure. I'm trying to reuse it as a fuction in order to save it in a table. I read about this on https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table. It's not accepting the declare after the return — Rock Road, Sep 18 '19 at 15:19
Msg 156, Level 15, State 1, Procedure Renetest, Line 12 [Batch Start Line 0] Incorrect syntax near the keyword 'Declare'. — Rock Road, Sep 18 '19 at 15:20
Have you read the MSDN documentation? Table-value function can only contain 1 statement. Read the documentation about different type of functions. — Eric, Sep 18 '19 at 16:01
so, if it cannot be turned into a function, any other ideas to save it on a table ? — Rock Road, Sep 18 '19 at 19:00
@RockRoad You still can use function, just not inline table-valued function. Use the multi statement table valued function. — Eric, Sep 18 '19 at 21:44
Tried it that way and i made some progress, still stuck on the final select. I have updated the code — Rock Road, Sep 19 '19 at 16:00
What do you mean by stuck? Stuck how? Error out? Wrong value? — Eric, Sep 19 '19 at 16:18
Msg 444, Level 16, State 2, Procedure Renetest, Line 81 [Batch Start Line 0] Select statements included within a function cannot return data to a client. — Rock Road, Sep 19 '19 at 17:26

score 0 · Answer 1 · answered Sep 18 '19 at 15:43

0

The function returns a table. Just select from the function with an INSERT statement.

INSERT INTO dbo.MyTable
SELECT Company, BookID
  FROM Renetest();

This is strictly what you asked. But this could be converted into a stored procedure and avoid using a function all together.

answered Sep 18 '19 at 15:43

Ricardo C

2,205
20
24

the function doesn't work just yet, i have the error: Incorrect syntax near the keyword 'Declare'. – Rock Road Sep 18 '19 at 18:59
@RockRoad the keyword RETURN after 'AS' should be right before the last SELECT. – Ricardo C Sep 18 '19 at 21:13
Still getting invalid sintax for declare – Rock Road Sep 18 '19 at 21:27

How to convert a query to a a function

1 Answers1