Can't Create Stored Procedure with Table Output Parameter

Can't create stored procedure with table output parameter

Table parameters are readonly. You cannot select into them. Use Table-Valued Parameters:

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

And Table-Valued Parameters:

You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

Read Arrays and Lists in SQL Server for a comprehensive discussion on alternatives.

Returning output parameter from stored procedure

You need to pass parameters, as neither have default values. As @p_prefix is an OUTPUT parameter, in order to consume that value, you need to declare a variable to pass to the parameter:

DECLARE @p_prefix varchar(3);
EXEC SFT.usp_GetPrefix @p_table = 'MAC_CHEESE', @p_prefix OUTPUT;

--PRINT @p_prefix; --Or whatever you're going to do with the value.

As a side note, you can make your Procedure considerably shorter:

CREATE PROCEDURE SFT.usp_GetPrefix @p_table  VARCHAR(30), @p_prefix VARCHAR(3) OUTPUT AS
BEGIN

    SET @p_prefix = CASE LEFT(@p_table,3) WHEN 'MAC' THEN 'MAC' ELSE 'UNK' END;

END

How to get output parameter and also a table when executing a stored procedure

The parameter value won't be available until after you consume the resultset, eg

var cmd0 = new SqlCommand("create or alter procedure pFoo @id int output as begin  select * from sys.objects; set @id = 12; end", con);
cmd0.ExecuteNonQuery();

var cmd = new SqlCommand("pFoo", con);
cmd.CommandType = CommandType.StoredProcedure;

var p1 = cmd.Parameters.Add("@id", SqlDbType.Int);
p1.Direction = ParameterDirection.Output;

var dt = new DataTable();
using (var rdr = cmd.ExecuteReader())
{                    
    dt.Load(rdr);
    var id = (int)p1.Value;
}

Can I have an optional OUTPUT parameter in a stored procedure?

Both input and output parameters can be assigned defaults. In this example:

CREATE PROCEDURE MyTest
  @Data1 int
 ,@Data2 int = 0
 ,@Data3 int = null output

AS

PRINT @Data1
PRINT @Data2
PRINT isnull(@Data3, -1)

SET @Data3 = @Data3 + 1

RETURN 0

the first paramter is required, and the second and third are optional--if not set by the calling routine, they will be assigned the default values. Try messing around with it and the following test-call routine in SSMS using different values and settings to see how it all works together.

DECLARE @Output int

SET @Output = 3

EXECUTE MyTest
  @Data1 = 1
 ,@Data2 = 2
 ,@Data3 = @Output output

PRINT '---------'
PRINT @Output

Stored procedure with output parameters vs. table-valued function?

A table valued function can only be used within a scope of a single SELECT statement. It cannot perform DML, catch exceptions etc.

On the other hand, it can return a set which can immediately be joined with another recordset in the same query.

If you use DML or don't need to use the output parameters in the set-based statements, use a stored proc; otherwise create a TVF.

Output parameter value changes depending on error type

I have been through several iterations of this answer, as I have found out more. I think I have now come to a conclusion.

As OP has pointed out, the non-return of the updated parameter is because of the copy-in/copy-out operation of the OUTPUT parameters. However, I have found that a non-existent table error appears not to be picked up by TRY-CATCH inside the SP, but is caught by TRY-CATCH outside.

The following is an amended version of the second example, using TRY..CATCH to pinpoint where the error is picked up.

I used this code to run all variants of the SP:

DECLARE @v int, @result int = 0, @Flag int = 1;
SET @v = 1;
BEGIN TRY
    SET @Flag = 2;
    EXEC @Result = SP @v OUTPUT;
    SET @Flag = 3;
END TRY
BEGIN CATCH
    PRINT ' C:   @Flag=' + CAST( @Flag AS Varchar(10) );

    SET @Flag = 4;

END CATCH

PRINT  ' D:      @v=' + CAST( @v AS Varchar(10) );
PRINT  ' E: @Result=' + CAST( @Result AS Varchar(10) );
PRINT  ' F:   @Flag=' + CAST( @Flag AS Varchar(10) );

The first version of the SP is:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

The output is:

>> SP
  A: @p1=11
  C:   @Flag=2
  D:      @v=1
  E: @Result=0
  F:   @Flag=4

So it appears that, even though the output parameter @p1 is updated within the SP, the value of the parameter is not passed back to the caller. The non-existent table error is not trapped by the TRY-CATCH inside the SP and by-passes any updates to OUTPUT parameters, but it is caught by the TRY-CATCH outside the SP.

Now, having tested that, I added some additional code into the SP:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'dbo' 
             AND  TABLE_NAME = 'nonExistentTable'))

    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

with this result:

>> SP
  A: @p1=11
  B: @p1=11
<< SP
 D:      @v=11
 E: @Result=0
 F:   @Flag=3

which is intriguing. So I changed the SP again, this time to select rows from a table that I know contains data:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    IF EXISTS( SELECT * FROM Data_Table )
    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

In this case the result is:

>> SP
  A: @p1=11
 C:   @Flag=2
 D:      @v=1
 E: @Result=0
 F:   @Flag=4

Finally a check on a completely unrelated table that is empty:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    IF EXISTS( SELECT * FROM Empty_Table )
    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

with the result:

>> SP
  A: @p1=11
  B: @p1=11
<< SP
 D:      @v=11
 E: @Result=0
 F:   @Flag=3

Someone who knows more about how the internals of SQL Server work might be able to explain and, perhaps, justify these results.

C# & SQL Server stored procedures - can't get output parameter value, return value works

Not sure if you can assign count(*) to a parameter. It only works in the sql statement.

Perhaps try this

Create procedure sp_CountEachNumberOccurencesOutput
    @number tinyint, @count_number int output
    As
    SET NOCOUNT ON
    select @counter_number=count(*)  from RawData
                where Number1 = @number or Number2 = @number  or Number3 = @number

Stored procedure output variable being stored as input/output type

As @GSerg commented, you can't.

In SQL, an OUTPUT parameter acts as both INPUT and OUTPUT, see this answer

You're obtaining an INOUT value because of the mapping used to refer ORACLE procedure parameter types, which are IN, OUT and INOUT.

From dba-oracle.com:

A variable passed as mode IN is always read-only. A variable
using IN mode can be read and used by the procedure/function but can
not be changed and it cannot be the receiver of an assignment
operation.

A variable passed in OUT mode is used to pass information back from
the procedure to the calling program. It is a write-only variable and
has no value until the block assigns it a value.

A variable passed in INOUT mode has characteristics of both the IN and
the OUT mode. The variable value is passed in and can be read by the
procedure. The procedure can also change the value and it will be
copied back to the passed variable when the procedure completes.

MySQL stored procedure: OUT parameter not being set

Typically, spent 3 hours on this, then JUST after I posted the question I find the problem. So, for future reference: It appears MySQL is case insensitive where variables are concerned. The ID column name and id variable apparently completely confused it.

I changed the procedure's input parameter name to retId and then it worked perfectly.

SQL server stored procedure return a table

A procedure can't return a table as such. However you can select from a table in a procedure and direct it into a table (or table variable) like this:

create procedure p_x
as
begin
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t values('a', 1,1,1)
insert @t values('b', 2,2,2)

select * from @t
end
go

declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t
exec p_x

select * from @t

---

Related Topics