Return multiple values from a SQL Server function

Question

How can I remove all background="..." in all HTML tags? I understood that it's not a good idea to try parsing HTML with regexes, but I don't know any over way. (tried regex "background([\s\S]*?)(;|\")" - doesn't work). Thanks.

Solution 1

Change it to a table-valued function

Please refer to the following link, for example.

Solution 2

Another option would be to use a procedure with output parameters - Using a Stored Procedure with Output Parameters

Solution 3

Here's the Query Analyzer template for an in-line function - it returns 2 values by default:

-- =============================================  
-- Create inline function (IF)  
-- =============================================  
IF EXISTS (SELECT *   
   FROM   sysobjects   
   WHERE  name = N'<inline_function_name, sysname, test_function>')  
DROP FUNCTION <inline_function_name, sysname, test_function>  
GO  

CREATE FUNCTION <inline_function_name, sysname, test_function>   
(<@param1, sysname, @p1> <data_type_for_param1, , int>,   
 <@param2, sysname, @p2> <data_type_for_param2, , char>)  
RETURNS TABLE   
AS  
RETURN SELECT   @p1 AS c1,   
        @p2 AS c2  
GO  

-- =============================================  
-- Example to execute function  
-- =============================================  
SELECT *   
FROM <owner, , dbo>.<inline_function_name, sysname, test_function>   
    (<value_for_@param1, , 1>,   
     <value_for_@param2, , 'a'>)  
GO  

Solution 4

Erland Sommarskog has an exhaustive post about passing data in SQL Server located here:

http://www.sommarskog.se/share_data.html

He covers SQL Server 2000, 2005, and 2008, and it should probably be read in its full detail as there is ample coverage of each method's advantages and drawbacks. However, here are the highlights of the article (frozen in time as of July 2015) for the sake of providing search terms that can be used to look greater details:

This article tackles two related questions:

• How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored
  procedure in a SELECT statement?
• How can I pass a table data in a parameter from one stored procedure to another?

OUTPUT Parameters

• Not generally applicable, but sometimes overlooked.

Table-valued Functions

• Often the best choice for output-only, but there are several restrictions.
• Examples:
  • Inline Functions: Use this to reuse a single SELECT.
  • Multi-statement Functions: When you need to encapsulate more complex logic.

Using a Table

• The most general solution. My favoured choice for input/output scenarios.
• Examples:
  • Sharing a Temp Table: Mainly for a single pair of caller/callee.
  • Process-keyed Table: Best choice for many callers to the same callee.
  • Global Temp Tables: A variation of process-keyed.

Table-valued Parameters

• Req. Version: SQL 2008
• Mainly useful when passing data from a client.

INSERT-EXEC

• Deceivingly appealing, but should be used sparingly.

Using the CLR

• Req. Version: SQL 2005
• Complex, but useful as a last resort when INSERT-EXEC does not work.

OPENQUERY

• Tricky with many pitfalls. Discouraged.

Using XML

• Req. Version: SQL 2005
• A bit of a kludge, but not without advantages.

Using Cursor Variables

• Not recommendable.

Solution 5

Example of using a stored procedure with multiple out parameters

As User Mr. Brownstone suggested you can use a stored procedure; to make it easy for all i created a minimalist example. First create a stored procedure:

Create PROCEDURE MultipleOutParameter
    @Input int,
    @Out1 int OUTPUT, 
    @Out2 int OUTPUT 
AS
BEGIN
    Select @Out1 = @Input + 1
    Select @Out2 = @Input + 2   
    Select 'this returns your normal Select-Statement' as Foo
          , 'amazing is it not?' as Bar

    -- Return can be used to get even more (afaik only int) values 
    Return(@Out1+@Out2+@Input)
END 

Calling the stored procedure

To execute the stored procedure a few local variables are needed to receive the value:

DECLARE @GetReturnResult int, @GetOut1 int, @GetOut2 int 
EXEC @GetReturnResult = MultipleOutParameter  
    @Input = 1,
    @Out1 = @GetOut1 OUTPUT,
    @Out2 = @GetOut2 OUTPUT

To see the values content you can do the following

Select @GetReturnResult as ReturnResult, @GetOut1 as Out_1, @GetOut2 as Out_2 

This will be the result:

View more solutions

Comments

[Fandango68]

I am sorry, but again the question is in relation to Functions, not stored procedures, and it seems the only way to answer the question is to use Table-Valued functions, as explained above.

[Cameron Lee]

@Fernando68: So? Functions and Stored Procedures are quite similar in T-SQL. Depending on where the OP is using the function, it might be possible to convert it into a Stored Procedure. If so, then this question would be an example of the xy problem and this answer useful to the actual problem at hand.

[levkaster]

I would prefer to remove background. Adding a transparent value will override main CSS settings.

[jbutler483]

You did read your snippet, right? "background" isn't supported in html5

[Kaiido]

@jbutler but if you read the question, it seems that's what the OP wants to remove

[jbutler483]

@Kaiido: However, there is a 'background' in css as well. There is no reference to this background or the css background in the question, so (presently) it could be either.

[Kaiido]

document.childNodes seems to be faster than document.querySelectorAll(*); jsperf.com/childnodes-vs-queryselectorall

[Kaiido]

@jbutler well yes there is : How can I remove all background="..." in all HTML tags? if it was css, it would have been background:... or [element.style.]background="..."but that would have been in js not in HTML tags

[levkaster]

There is a little problem than it encounters something like: <p><span style="background="...">text</span></p>

[Afzal Ahmad]

Yes, because document.childNodes is a property stub while document.querySelectorAll(*); is a function stub that give results after execution, Thats why property stub is always faster then others,

[Hacketo]

"background=.." could be only on the body, right ? (in case of attribute)

[Jose Manuel Ojeda]

this example is great thanks for the link. is it there a way to return rows from multiple tables? what I want is to return 1 row for table A, 1 row from table B... and so on