[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

function if

In sql 2008 I call a function from stored procedure.

I would like to have an if statement in the function. Following returns error. How is best to do this?

ALTER FUNCTION [dbo].[myFunction](@GId int)

RETURNS TABLE
AS
      RETURN(

                     if @GId >0
                        begin
                                --...select statement
                        end
                    else
                      begin
                                 .....
                      end
        )
0
johnkainn
Asked:
johnkainn
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot have a untyped table value function have a IF like this.
see the specs from here:
http://msdn.microsoft.com/en-us/library/ms186755.aspx

either you return
#option 1 : a single value,
#option 2 : a single SELECT, untyped
#option 3 : a typed table structure, in which case you define the @table variable, and INSERT into that what you need, based on your IF statements.

"if" your select statements return the same columns etc, you could create with option #2 a UNION ALL query based on the condition

ALTER FUNCTION [dbo].[myFunction](@GId int)

RETURNS TABLE
AS
      RETURN(
           SELECT ...
              FROM ...
           WHERE ...
               AND ( @GId >0 )
       UNION ALL
           SELECT ...
              FROM ...
           WHERE ...
               AND ( @GId <=0 )
        ) 

Open in new window

0
 
johnkainnAuthor Commented:
Thank you.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now