Declare table variable in a UDF to enter table name as a parameter

Question

I am working on a query and created a function to fetch result from outcomes table susing the following code.

CREATE FUNCTION dbo.Shippad (@tbl NVARCHAR(30))
RETURNS TABLE
AS
    RETURN
      SELECT LEFT(ship, Charindex(' ', ship) - 1) + ' '
             + Replicate('*', Charindex(' ', Substring(ship, Charindex(' ', ship) + 1, Len(ship))) + 1 -2)
             + ' '
             + Reverse(LEFT(Reverse(ship), Charindex(' ', Reverse(ship)) - 1)) as final_work 
      FROM   outcomes
      WHERE  Charindex(' ', Substring(ship, Charindex(' ', ship) + 1, Len(ship))) > 1

The problem of the above code is that it doesn't matter what parameter I entered during the execution, it always gives the result as long as the parameter I entered is a valid table name in the database.

Right now I am wondering if I could create a parameter to enter a table name into FROM part, that way only when I enter outcomes it would display the result.

I tried to declare a table variable using the following code:

declare @ship_outcome table
(   final_work nvarchar(30)
)

insert into @ship_outcome (final_work)
select 
        left(ship, charindex(' ', ship) - 1) + ' ' + 
        replicate('*', charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) + 1 -2) + ' ' +
        reverse(left(reverse(ship), charindex(' ', reverse(ship)) - 1))
from outcomes
where charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) > 1;

select * from @ship_outcome

However I am not sure how to consolidate the table variable into the UDF. Please help.

It is not clear. Do you want to get result only when `@tbl = outcomes` ? — Pரதீப், Mar 28 '16 at 01:48
In that case I will suggest you not to call this function itself — Pரதீப், Mar 28 '16 at 01:53
@Prdp yes I am planing to show the result only when `@tbl = outcomes`. BTW thanks for the query you gave yesterday, which exactly is the query for the UDF creation. — PURWU, Mar 28 '16 at 02:03

sam · Accepted Answer · 2016-03-28T15:42:43.360

You can also achieve the result using IF statement in Multi statement table valued function like mentioned below : -

CREATE FUNCTION dbo.Shippad (@tbl NVARCHAR(30))
RETURNS @t table
(final_work nvarchar(30))
AS
begin
if @tbl = 'outcomes'
begin
      Insert into @t
      SELECT LEFT(ship, Charindex(' ', ship) - 1) + ' '
             + Replicate('*', Charindex(' ', Substring(ship, Charindex(' ', ship) + 1, Len(ship))) + 1 -2)
             + ' '
             + Reverse(LEFT(Reverse(ship), Charindex(' ', Reverse(ship)) - 1)) as final_work 
      FROM   outcomes
      WHERE  Charindex(' ', Substring(ship, Charindex(' ', ship) + 1, Len(ship))) > 1 
end
return
end

Note - Performance of Multi statement table value function is horrible for large records. It is advisable to use Inline function by using @tbl = 'outcomes' in where clause in inline table value function like -

CREATE FUNCTION dbo.Shippad (@tbl NVARCHAR(30))
RETURNS TABLE
AS
    RETURN
      SELECT LEFT(ship, Charindex(' ', ship) - 1) + ' '
             + Replicate('*', Charindex(' ', Substring(ship, Charindex(' ', ship) + 1, Len(ship))) + 1 -2)
             + ' '
             + Reverse(LEFT(Reverse(ship), Charindex(' ', Reverse(ship)) - 1)) as final_work 
      FROM   outcomes
      WHERE  Charindex(' ', Substring(ship, Charindex(' ', ship) + 1, Len(ship))) > 1 and @tbl = 'outcomes'

thanks for the solution. as you mentioned about adding a condition to the `WHERE` statement, do I just do that by adding `AND @tbl = 'outcomes'` ? — PURWU, Mar 28 '16 at 13:18
I have added the condition @tbl = 'outcomes' in the where clause of inline table value function. Please see my updated answer — sam, Mar 28 '16 at 15:19
hi thanks for the updates. one more thing I just noticed is that i guess an `insert` statement after `begin` is necessary — PURWU, Mar 28 '16 at 15:26
Yes you are correct. I forgot to type insert statement. I have updated my answer. — sam, Mar 28 '16 at 15:43

Declare table variable in a UDF to enter table name as a parameter

1 Answers1