Can't get table function to work with if statement

Question

trying to create a table function that returns a two column table that must be conditioned with an if statement.

I have two other functions that do this separate but cant get it to work when combined.

create  function dbo.PDM_GetCategorytable (@Mystring varchar(200) ,@appid int)
returns table
as -- begin

--return
begin
-- declare @MyString varchar(200);
--set @MyString= 'Fred mtf is dead'--'RE: [Encrypt]]FW: MTF Military --- 
--UPDATE URGENT'
--declare @AppId int =5

declare @Dubb Table (Col varchar(200));
insert into @dubb 
(Col )
values ( @Mystring);

if ((select top 1 categoryid from @dubb a
left join 
(SELECT        (pass.Recordid) as categoryid , pass.appid, 
pass.Priority_Identifier, pass.PrioritySortorder, pass.Category, 
ps.Search_String AS SrchVar
FROM            dbo.PDM_PriorityAssignments AS pass INNER JOIN
                     dbo.PDM_Priority_Search AS ps ON pass.Recordid = 
ps.PriorityAssignmentid where pass.appid=@AppId )  b on a.col like '%' + 
b.SrchVar + '%'
                     order by PrioritySortorder)  is not null)--'where 
appid=@AppId
begin
select top 1 categoryid,Category from @dubb a
left join 
(SELECT        (pass.Recordid) as categoryid , pass.appid, 
pass.Priority_Identifier, pass.PrioritySortorder, pass.Category, 
ps.Search_String AS SrchVar
FROM            dbo.PDM_PriorityAssignments AS pass INNER JOIN
                         dbo.PDM_Priority_Search AS ps ON pass.Recordid = 
ps.PriorityAssignmentid where pass.appid=@AppId )  b on a.col like '%' + 
b.SrchVar + '%'
                         order by PrioritySortorder;
                    end
else
begin
select recordid as categoryid,Category  FROM dbo.PDM_PriorityAssignments 
AS pass  where appid=@AppId and Priority_Identifier like 'Routine'
end
return
end;

expected results will be the returning of two columns , category id, and category.

Could you please use proper formatting for this code? As it is, it seams that it will not even run. — Eduardo Silva, May 31 '19 at 14:54
not sure what you mean but I understand it won't run, that is why i posted the question — schalld, May 31 '19 at 15:15
take a look at this https://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function — Daniel N, May 31 '19 at 15:35

score 0 · Answer 1 · answered May 31 '19 at 15:37

seems like a duplicate for Multi statement table valued function Multi-statement Table Valued Function vs Inline Table Valued Function

here is an example how to achieve this.

CREATE FUNCTION foo
    (@param int)
RETURNS 
    @T TABLE (id int, val varchar)
As
BEGIN


    IF (@param = 1)
        insert into @t Select 1, 'a';
    else
        insert into @t Select 0, 'a';

    return ;

END
GO

score 0 · Answer 2 · answered May 31 '19 at 15:40

The problem is exactly because of the algorithm you need to use. That requirement prevents you from returning just a table. On this scenarios you need to do something a bit different. First define the resulting table and then, insert the values as you need.
I can´t run it without your data but it should work.

CREATE FUNCTION dbo.PDM_GetCategorytable (
    @Mystring VARCHAR(200)
    ,@appid INT
    )
RETURNS @GetCategorytable TABLE
(
    [CategoryId] INT,
    [Category] VARCHAR(200))
AS -- begin
--return
BEGIN
    -- declare @MyString varchar(200);
    --set @MyString= 'Fred mtf is dead'--'RE: [Encrypt]]FW: MTF Military --- 
    --UPDATE URGENT'
    --declare @AppId int =5
    DECLARE @Dubb TABLE (Col VARCHAR(200));

    INSERT INTO @dubb (Col)
    VALUES (@Mystring);

    IF (
            (
                SELECT TOP 1 categoryid
                FROM @dubb a
                LEFT JOIN (
                    SELECT (pass.Recordid) AS categoryid
                        ,pass.appid
                        ,pass.Priority_Identifier
                        ,pass.PrioritySortorder
                        ,pass.Category
                        ,ps.Search_String AS SrchVar
                    FROM dbo.PDM_PriorityAssignments AS pass
                    INNER JOIN dbo.PDM_Priority_Search AS ps ON pass.Recordid = ps.PriorityAssignmentid
                    WHERE pass.appid = @AppId
                    ) b ON a.col LIKE '%' + b.SrchVar + '%'
                ORDER BY PrioritySortorder
                ) IS NOT NULL
            ) --'where 
        -- NOT SURE WHAT YOU ARE TRYING TO DO WITH THIS -- appid = @AppId
    BEGIN
        insert into @GetCategorytable(Categoryid, Category)
        SELECT TOP 1 categoryid
            ,Category
        FROM @dubb a
        LEFT JOIN (
            SELECT (pass.Recordid) AS categoryid
                ,pass.appid
                ,pass.Priority_Identifier
                ,pass.PrioritySortorder
                ,pass.Category
                ,ps.Search_String AS SrchVar
            FROM dbo.PDM_PriorityAssignments AS pass
            INNER JOIN dbo.PDM_Priority_Search AS ps ON pass.Recordid = ps.PriorityAssignmentid
            WHERE pass.appid = @AppId
            ) b ON a.col LIKE '%' + b.SrchVar + '%'
        ORDER BY PrioritySortorder;
    END
    ELSE
    BEGIN
        insert into @GetCategorytable(Categoryid, Category)
        SELECT recordid AS categoryid ,Category FROM dbo.PDM_PriorityAssignments AS pass
        WHERE appid = @AppId
            AND Priority_Identifier LIKE 'Routine'
    END

RETURN
END;

it compiles not but i get an error executing it. SELECT Recordid, ReceivedDate, [From], Subject, Size, [dbo].PDM_GetCategorytable([subject],5) FROM epif_inventory20190528 — schalld, May 31 '19 at 16:01
get the following error:Cannot find either column "dbo" or the user-defined function or aggregate "dbo.PDM_GetCategorytable", or the name is ambiguous. — schalld, May 31 '19 at 16:03
try simple Select * from dbo.PDM_GetCategorytable('Inserthereyourstring', inserthereyourappid), passing the values you want and it should return results. — Eduardo Silva, May 31 '19 at 16:07

Can't get table function to work with if statement

2 Answers2