SQL Server – Stored Procedure with Output Parameter

parametersql serverstored-procedurest-sql

I know how to write a stored procedure with output parameters. But I don't know the reason why I would use it over just using a simple SELECT statement. A normal stored procedure can still return an output in a grid (see below examples).

Can anyone give a real life example where I can use an SP with output parameter over with SP without output parameter?

Examples

-- Using output parameter
SELECT @var = COUNT(*) FROM table1 WHERE gender = @gender...

-- Without output parameter
SELECT COUNT(*) FROM table WHERE gender = @gender...

Best Answer

Output parameters in stored procedures are useful for passing a value back to the calling T-SQL, which can then use that value for other things.

Let's say you have a stored procedure that returns a state given the input of a city, with state as the output parameter:

CREATE PROCEDURE [dbo].[GetStateFromCity] (@City NVARCHAR(30), @State NCHAR(2) OUTPUT)
AS
SELECT @State = [State]
FROM [ExampleTable] 
WHERE [City] = @City
GO;

You can now use this output parameter to pass in a value elsewhere.

For example:

DECLARE @State int
EXEC [dbo].[GetStateFromCity] @City = 'Calgary', @State OUTPUT;

-- Do something with this value
SELECT @State;

-- Do something else
EXEC [dbo].[GetInsuranceCompanyByState] @State;

To summarise, if you just want to return a value for a client application, you probably don't need an output parameter.

However, if you want to pass values around in T-SQL between stored procedures, they can be very useful.

For what it's worth, I hardly use output parameters.

Related Solutions

SQL Server – Stored Procedure Not Showing Results

You need to split your parameters into rows and use where ... in as a check.

Using a split function like this one your code could look like this.

CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS 
BEGIN

    DECLARE @ProjHomeGrpTbl TABLE (Value NVARCHAR(4))

    INSERT INTO @ProjHomeGrpTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHomeGrp);

    DECLARE @ProjHierTbl TABLE (Value NVARCHAR(5))

    INSERT INTO @ProjHierTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHier);

    SELECT  [Capital Project] ,
            [Capital Task] ,
            ResourceName ,
            ProjectName ,
            [Project Home Group] ,
            ActualWork ,
            TimeByDay ,
            ResourceStandardRate ,
            ActualWork * ResourceStandardRate AS Dollars ,
            [Project Hierarchy]
    FROM    [IR.CapOnly]
    WHERE   ( TimeByDay >= @StartDate )
            AND ( [Project Home Group] in (SELECT Value FROM @ProjHomeGrpTbl) )
            AND ( TimeByDay <= @EndDate )
            AND ( ActualWork > 0 )
            AND ( [Project Hierarchy] in (SELECT Value FROM @ProjHierTbl) )
    ORDER BY ProjectName ,
            ResourceName

END

Oracle 10g Stored Procedures – Finding Parameter Names for Binding

I did a little bit more research and found the answer to the problem.

Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text').

The actual parameter names for stored procedures can be discovered using the following query:

SELECT 
    argument_name, 
    in_out, 
    data_type 
FROM 
    all_arguments 
WHERE 
    owner = 'SYS' --owner for said procedure
    AND package_name = 'DBMS_OUTPUT' --if procedure is in a package that name, null otherwise
    AND object_name = 'PUT_LINE' --procedure name

The values SYS, DBMS_OUTPUT, and PUT_LINE can be replaced with the values of the the stored procedure that you would like to look up.

Examples

Best Answer

Related Solutions

SQL Server – Stored Procedure Not Showing Results

Oracle 10g Stored Procedures – Finding Parameter Names for Binding

Related Question