title: SQL Functions - If Statements
body: <p>So, I'm trying to write a function that will, depending on the length of the inputted number, call another function.</p>
  
  <p>The two functions shown both return a table.</p>
  
  <p>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?!</p>
  
  <p>Also, it suggests ('it' being SQL Server Management Studio 2008 R2) that the syntax near the IF is incorrect, expecting '(', SELECT or WITH</p>
  
  <p>Here's my query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which looks like this as a function:</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <p>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?</p>
  
  <p>Thanks in advance!</p>