How to call SQL functions from another SQL function

Question

In my database i have two types of applications: with serial number and without serial number. I want to create a function in SQL Server that takes as parameters 2 nvarchars: first is the application code and the other is the serial number and returns a table type with the available versions. This function should first check if the application has serial number or not and depending on the answer, which is bit type, call the proper function. How can i put this together?

table getVersions(appCode, serial){
  if(isFree(appCode)){
     call getAppVersionsFree(appCode); 
  } else {
     call getAppVersions(appCode,serial);
  }
return table;

The function that checks if the application has serial number or is free:

create function dbo.isFree(
   @appCode nvarchar(128))
returns bit
as 
begin
   declare @ret bit
   select @ret = (select a.IsFree from Application a where a.Code=@appCode)
   return @ret
end

Function to be called if the application is free:

create function dbo.getAppVersionsFree
(
   @appCode nvarchar(128))
returns table as
return (
   select v.Version from Version v 
   inner join Application a on a.Code = v.AppCode
   inner join SerialNumber s on a.Code = s.AppCode
   where a.Code = @appCode 
)

Function to be called if the application is not free (with serial number):

create function dbo.getAppVersions
(
   @appCode nvarchar(128),
   @serialNo nvarchar(128))
   returns table as
   return (
      select v.Version from Version v 
      inner join Application a on a.Code = v.AppCode
      inner join SerialNumber s on a.Code = s.AppCode
      where a.Code = @appCode and s.SerialNo = @serialNo
)

EDIT:

How to make this to work?

create function dbo.getVersions
(
   @appCode nvarchar(128),
   @serialNo nvarchar(128))   
returns table 
as
begin
declare @returnTable table (Version nvarchar(10))
if(dbo.isFree(@appCode) = 0) begin  
    set @returnTable = select dbo.getAppVersions(@appCode,@serialNo)
    end
else begin
   set @returnTable = select dbo.getAppVersionsFree(@appCode)
   end
return @returnTable;
end

Do you want to do this in SQL Server as a new table valued function, or it should be implemented in the application? — Pred
– Pred, Commented Jun 5, 2014 at 14:34
@Pred The return value of the function should be a table with versions — laura
– laura, Commented Jun 5, 2014 at 14:50

Pred · Accepted Answer · 2014-06-05 14:58:22Z

1

You have two options (in SQL):

The first option is to write a Multistatement Table-valued Function. The syntax is the following (from MSDN):

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body
        RETURN
    END
[ ; ]

In this case you should INSERT the selected records into the declared return (table) variable. You can write your logic in Multistatement functions.

The trick is in the line where the RETURN declaration lives:

RETURNS @return_variable TABLE <table_type_definition>

In your case, this will be something like this:

RETURNS @returnVersions TABLE (Version INT)

And the logic is something like this:

IF (dbo.IsFree( ... )) BEGIN
    INSERT INTO @returnVersion (Version) SELECT Version FROM dbo.GetVersionsFree( ... )
END
ELSE BEGIN
    INSERT INTO @returnVersion (Version) SELECT Version FROM dbo.GetVersions( ... )
END

The second option is to modify your function to handle both cases like this:

SELECT
    v.Version
FROM
    Version v 
    INNER JOIN Application a
        ON a.Code = v.AppCode
WHERE
    a.Code = @appCode
    AND (
        -- The given application is free
        a.IsFree = 1

        -- The serial number should exist when the application is not free
        OR (
            a.IsFree = 0
            AND EXISTS (
                SELECT 1
                FROM SerialNumber s
                WHERE a.Code = s.AppCode AND s.SerialNo = @serialNo
            )
        )
    )

(Please note that I did not test this select statement, so there could be typos)

edited Jun 5, 2014 at 14:58

answered Jun 5, 2014 at 14:45

Pred

9,0903 gold badges28 silver badges47 bronze badges

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

4 Comments

laura Over a year ago

The functions getAppVersions(appCode, serialNo) and getAppVersionsFree(appCode) are returning tables. So i can't call them to make an insert into the table because are returning multiple values.

Pred Over a year ago

Tables can hold multiple values. The above mentioned insert is a standard INSERT... SELECT statement. Did you tried it?

laura Over a year ago

I edited the question. Please have a look and tell me if it is possible to do something like that. Thank you

Pred Over a year ago

Yes, that is possible, and my answer still stands. The second option does that in one query. The first one is the skeleton of the new function you are trying to write.

Collectives™ on Stack Overflow

How to call SQL functions from another SQL function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related