CODEWITHSUNDEEP
sql-servert-sql
lanes123
lanes123

Reputation: 147

SQL Server: creating functions

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

Upvotes: 0

Views: 2390

Answers (1)

M.Ali
M.Ali

Reputation: 69564

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

Upvotes: 3

Related Questions