SQL Server: creating functions

Question

I am trying to apply some logic to my select by creating the following function:

ALTER FUNCTION [dbo].[SelectemnExportObject]
    (@TITLE NVARCHAR(MAX)) 
RETURNS NVARCHAR(20) 
AS
BEGIN
    DECLARE @Str NVARCHAR(MAX) = ''
    DECLARE @Definition1 VARCHAR(MAX) = (SELECT DEFINITION1 
                                         FROM [dbo].test 
                                         WHERE title = @Title)

    DECLARE @Definition2 VARCHAR(MAX) = (SELECT DEFINITION2 
                                         FROM [dbo].test 
                                         WHERE title = @Title)
    DECLARE @Definition3 VARCHAR(MAX) = (SELECT DEFINITION3 
                                         FROM [dbo].test 
                                         WHERE title = @Title)

    IF @Definition1  <> '' 
       SET @str = @Definition1
    ELSE IF @Definition2  <> '' 
       SET @str = @str + '<br />' + @Definition2
    ELSE IF @Definition3  <> '' 
       SET @str = @str + '<br />' + @Definition3

    RETURN @Str
END

Am I correct in saying, to call this function is as such?

select * 
from [dbo].[SelectemnExportObject]('absconding')

I am trying to create a row of information, cell 1 will contain @str, then will create another called @str2 and so on ..

Will I need to return something else if I want to accomplish this?

I appreciate the help, and apologies in advance if the tagging isn't correct

That function could be simplified considerably. For example, you can get multiple scalar values from a single select. No need to select the same table over and over. And since you want to end up with them all combined this could all be done in a single select statement. The upside of that approach is that it could be an inline table valued which is way faster and more flexible than a scalar function. — Sean Lange
– Sean Lange, Commented Apr 27, 2018 at 21:28
The function you created is a scalar function. It should be in SELECT clause, not FROM clause. — Eric
– Eric, Commented Apr 27, 2018 at 21:54

M.Ali · Accepted Answer · 2018-04-27 21:32:51Z

3

Because the function is returning a scalar value, it is a scalar function you would call it like:

select [dbo].[SelectemnExportObject]('absconding') AS [Str1]
      ,[dbo].[SelectemnExportObject]('abscondin2') AS [Str2]

If the function was returning a table (Inline table valued function or Multi-statement valued function) then you would need to call it/select from it as you were selecting from a table.

select * from [dbo].[SelectemnExportObject]('absconding')

Edit

To make your Function return multiple values you would need to convert this function into a multi-statement table valued function. The function definition would look something like....

CREATE FUNCTION [dbo].[SelectemnExportObject] ( @TITLE NVARCHAR(MAX) ) 
RETURNS @Result TABLE
(Str1    NVARCHAR(MAX)         NOT NULL,
 Str2    NVARCHAR(MAX)         NOT NULL)
AS
BEGIN

    DECLARE @Str1 NVARCHAR(MAX) = '';
    DECLARE @Str2 NVARCHAR(MAX) = '';

/* Populate the values of @Str1 and @Str2 how ever you need to */

 INSERT INTO @Result (Str1 , Str2)
 VALUES (@Str1 , @Str2)

    RETURN;
END

edited Apr 27, 2018 at 21:32

answered Apr 27, 2018 at 21:03

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

6 Comments

lanes123 Over a year ago

Okay thanks! what if i wanted to return a row which is created in the function? For example, if i store @str in 1 cell .. then in another cell a new value

M.Ali Over a year ago

@lanes123 then you select function twice separated with a comma as shown in my first example.

lanes123 Over a year ago

Sorry, i dont think i am explaining myself properly! I mean, if i want to return 2 strings in that one method .. or will i have to create a different function for the second string?

M.Ali Over a year ago

@lanes123 ah if you return more than 1 value from the function, either more columns or more than 1 value in one column, you will need to change the function definition and the function type, maybe a multi-statement table valued function. You will then do a SELECT * FROM [Schema].[Function]('param')

lanes123 Over a year ago

Would you be able to give an example to how i could tweak my function to return 2 values? Thanks for the help!

|

Collectives™ on Stack Overflow

SQL Server: creating functions

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related