SQL Functions - If Statements

Question

So, I'm trying to write a function that will, depending on the length of the inputted number, call another function.

The two functions shown both return a table.

Writing the following query executes without issue, however, as soon as I attempt to move it into a function (without the declare and set statements) I have errors. Namely, that I must "declare the scalar variable, @idNumber" - but given that it's a param, how?!

Also, it suggests ('it' being SQL Server Management Studio 2008 R2) that the syntax near the IF is incorrect, expecting '(', SELECT or WITH

Here's my query:

DECLARE @idNumber int;
SET @idNumber = 12534;

IF (@idNumber LIKE '_____')
BEGIN
    SELECT * FROM getBatch(@idNumber);
END
ELSE IF (@idNumber LIKE '____')
BEGIN
    SELECT * FROM getBlend(@idNumber);
END
ELSE
BEGIN
    SELECT 'You absolute melon - get your query right!' AS 'Seriously?'
END

Which looks like this as a function:

CREATE FUNCTION [dbo].[getBxBl] (@idNumber int)
RETURNS TABLE
AS
RETURN
(
    IF (@idNumber LIKE '_____')
    BEGIN
        SELECT * FROM getBatch(@idNumber);
    END
    ELSE IF (@idNumber LIKE '____')
    BEGIN
        SELECT * FROM getBlend(@idNumber);
    END
    ELSE
    BEGIN
        SELECT 'You absolute melon - get your query right!' AS 'Seriously?'
    END
);

In future there will be other functions I'm calling in this way, so, I just want to get to grips with a basic two case selection at the moment... Can anybody help me out on this one?

Thanks in advance!

you are declared @idnumber as int and then comparing it to varchar value. — Mukund
– Mukund, Commented Nov 11, 2014 at 10:43
Indeed I did, however, I would add that the query works - it just doesn't work as a function — Will Whitehead
– Will Whitehead, Commented Nov 11, 2014 at 11:00

Pரதீப் · Accepted Answer · 2014-11-11 11:39:20Z

1

If your function is going to have more than a select then you need to declare the return table and wrap the code inside BEGIN and END. Try this..

CREATE FUNCTION [dbo].[Getbxbl] (@idNumber INT)
RETURNS @OuTab TABLE (
  column1 VARCHAR(100))
AS
  BEGIN
      IF ( @idNumber LIKE '_____' )
        BEGIN
            INSERT @OuTab
            SELECT *
            FROM   Getbatch(@idNumber);
        END
      ELSE IF ( @idNumber LIKE '____' )
        BEGIN
            INSERT @OuTab
            SELECT *
            FROM   Getblend(@idNumber);
        END
      ELSE
        BEGIN
            INSERT @OuTab
            SELECT 'You absolute melon - get your query right!' AS 'Seriously?'
        END
  END

edited Nov 11, 2014 at 11:39

answered Nov 11, 2014 at 11:29

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

Collectives™ on Stack Overflow

SQL Functions - If Statements

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related