Convert stored procedure to table-valued query

Question

I have this procedure

ALTER PROCEDURE [dbo].GetHerdByUserProc(@user int)
As 
    begin
        Declare
            @GroupId uniqueidentifier,
            @UserTrade bit

        Set @GroupId = (select tbUser.group_id from tbUser where Userid = @user)
        Set @UserTrade = (select tbUser.isTrade from tbUser where Userid = @user)
        if @GroupId IS NOT NULL and @UserTrade = '1'
        Begin 
            select HerdId from tbUserHerds where tbUserHerds.UserId in (select Userid from tbUser where tbUser.Group_Id = @GroupId)
            return;
        END
        If @GroupId IS NOT NULL
        Begin   
            select HerdId from tbUserHerds where tbUserHerds.UserId = @user
            return;
        End
        return;
    End

It returns a list correctly except I would also like to run a query on the list that is returned and as far as I am aware I cannot write a query such as

Select * from GetHerdByUserProc 80

So I am looking the best way to convert this to a table-valued query.

I have changed the alter to say 'Create Function x(@user int) Returns Table As'

But that does not seam to work it starts screaming errors at me.

Any ideas? The Database Server is MSSQL2008

Answer 1

Based on your Syntax, I'm going to assume SQL Server for now.

From BOL the Syntax for an inline function should be...

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

With this format, you can not use SET, DECLARE, IF, etc. All you can use is a single SQL Statement. [If you need to use program flow, look to a multi-statement table valued function.]

It's a separate topic, but Inline Table Valued Functions have many performance efficiencies over their Multi-Statement equivilents. Nearly always, if you can do it Inline, you should do so.

As it happens, you can write your logic without the IF statements, and use just a single SQL Statement. This gives the following Inline Table Valued Function...

CREATE FUNCTION [dbo].GetHerdByUserProc(@user int)
RETURNS TABLE
RETURN
  SELECT
    herd.HerdID
  FROM
    tbUser          AS user
  INNER JOIN
    tbUser          AS group
      ON group.group_id = user.group_id
  INNER JOIN
    tbUserHerds     AS herd
      ON herd.UserID = group.UserID
  WHERE
        user.userID   = @userID
    AND user.isTrade  = 1
    AND user.group_id IS NOT NULL

  UNION ALL

  SELECT
    herd.HerdID
  FROM
    tbUser          AS user
  INNER JOIN
    tbUserHerds     AS herd
      ON herd.UserID = user.UserID
  WHERE
        user.userID    = @userID
    AND user.isTrade  <> 1
    AND user.group_id IS NOT NULL

The UNION ALL combined with the WHERE clauses effectively does your IF statements for you. (Do note, if user.isTrade can be NULL, you need to change user.isTrade <> 1 to something more like ISNULL(user.isTrade, 0) <> 1.)

Potentially, you could even simplify this down to a single query, though I would test it to see if it's actually any more efficient or not...

RETURN
  SELECT
    herd.HerdID
  FROM
    tbUser          AS user
  INNER JOIN
    tbUser          AS group
      ON (group.group_id = user.group_id AND user.isTrade = 1)
      OR (group.user_id  = user.userID)
  INNER JOIN
    tbUserHerds     AS herd
      ON herd.UserID = group.UserID
  WHERE
    user.userID = @userID

• if group_id is NULL, the first join will never succeed.
• then the two IF blocks are simulated by the OR.