T-SQL Multi-Statement Table-Valued Function

Question

I could really use some help writing a multi-statement table-valued function that uses the following logic:

Accepts a varchar parameter as input
If the parameter starts with A, run a select statement
If no rows are returned, run a select statement
If the parameter does not start with A, run a select statement
If no rows are returned, run a select statement
Returns an output table, which will be one of the result-sets from above depending on the parameter

So far, this is what I have:

CREATE FUNCTION dbo.CheckAccess
(@UserName varchar(30))
RETURNS @AccessTable TABLE (User varchar(max), Access varchar(max))
AS

BEGIN
   IF LEFT(@UserName,1) = 'A'
       INSERT INTO @AccessTable
       SELECT
           CASE
           WHEN EXISTS (SELECT User, Access FROM dbo.AccessTable1 WHERE User = @UserName)
           THEN (SELECT User, Access FROM dbo.AccessTable1 WHERE User = @UserName)
           ELSE (SELECT @UserName User, 'No Access' Access)
           END
   ELSE
      INSERT INTO @AccessTable
      SELECT
           CASE
           WHEN EXISTS (SELECT User, Access FROM dbo.AccessTable2 WHERE User = @UserName)
           THEN (SELECT User, Access FROM dbo.AccessTable2 WHERE User = @UserName)
           ELSE (SELECT @UserName User, 'No Access' Access)
           END
RETURN
END;

Not sure what I'm missing here, but I'm receiving the errors below:

Msg 116, Level 16, State 1, Procedure CheckAccess, Line 90
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Procedure CheckAccess, Line 92
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 213, Level 16, State 1, Procedure CheckAccess, Line 11
Column name or number of supplied values does not match table definition.

I know how to do this fairly easily in a stored procedure, using temp tables, but since they are not an option in functions, I'm having a bit of difficulty. If anyone can offer some suggestions, I'd really appreciate it. Thanks in advance.

Indi · Accepted Answer · 2013-10-03 20:55:34Z

1

You can simplify your function using @@rowcount server variable as:

CREATE FUNCTION dbo.CheckAccess
   (@UserName varchar(30))
   RETURNS @AccessTable TABLE (User varchar(max), Access varchar(max))
AS

BEGIN
   IF LEFT(@UserName,1) = 'A'
       INSERT INTO @AccessTable
        SELECT User, Access FROM dbo.AccessTable1 WHERE User = @UserName
   ELSE
      INSERT INTO @AccessTable
        SELECT User, Access FROM dbo.AccessTable2 WHERE User = @UserName

   --@@rowcount gives number of records inserted/affected from immediately previous query
   IF @@RowCount = 0 
      INSERT INTO @AccessTable
       SELECT @UserName User, 'No Access' Access

   RETURN

END;

edited Oct 3, 2013 at 20:55

answered Oct 3, 2013 at 20:50

Indi

33.9k7 gold badges61 silver badges80 bronze badges

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

Comments

Der U · Accepted Answer · 2013-10-03 21:22:54Z

instead of a boolean check like this

EXISTS (SELECT User, Access FROM dbo.AccessTable1 WHERE User = @UserName)

try

EXISTS (SELECT 1 FROM dbo.AccessTable1 WHERE User = @UserName)

generally an EXISTS expression does not need specific results, it just needs to check wether NULL is returned because no row could be found or wether something could be returned no matter what that is. It's a boolean expression that evaluates to yes or no. The '1' suffices for that purpose. The first two error messages are about that.

The next problem is that a CASE-expression by definition can only define one field and one field only. It can't return the needed two fields. Thats what the last error message is propably about, the function is defined as returning two fields. You could now write two CASE-expressions seperated by a comma.

I would suggest a different approach for Your logic though: within your IF-statment do sub-IFs, like nested IFs. I have not tested this, just an idea:

   IF LEFT(@UserName,1) = 'A'
     IF EXISTS (SELECT 1 FROM dbo.AccessTable1 WHERE User = @UserName)
       INSERT INTO @AccessTable
       SELECT User, Access FROM dbo.AccessTable1 WHERE User = @UserName
     ELSE (SELECT @UserName User, 'No Access' Access)

   ELSE
     IF EXISTS (SELECT 1 FROM dbo.AccessTable2 WHERE User = @UserName)
      INSERT INTO @AccessTable
      SELECT User, Access FROM dbo.AccessTable2 WHERE User = @UserName
     ELSE (SELECT @UserName User, 'No Access' Access)

or another idea that propably performs better but I am not sure whether this meets your requirement for the field 'Access' 100 percent. If @username is present in the table will ther allways be content in the Access-field?

   IF LEFT(@UserName,1) = 'A'
       SELECT 
         ISNULL(User,@UserName) User
         ,ISNULL(Access,'No Access') Access 
       FROM dbo.AccessTable1 WHERE User = @UserName
   ELSE
       SELECT 
         ISNULL(User,@UserName) User
         ,ISNULL(Access,'No Access') Access 
       FROM dbo.AccessTable2 WHERE User = @UserName

One additional remark: an IF (also ELSE) will only consider one following statement. Should there be more than one statement to be run you would need to encapsule them by BEGIN and END like so

IF @fact=1
  BEGIN
    statement 1
    .
    .
    statement n
  END
ELSE
  BEGIN
    statement 1
    .
    .
    statement n
  END

Collectives™ on Stack Overflow

T-SQL Multi-Statement Table-Valued Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related