SQL Server output parameter issue
The confusion is justified to a degree - and other RDBMS like Oracle do have stored procedure parameters which can be of type IN (input only), OUT (output only), or INOUT (both ways - "pass by reference" type of parameter).
SQL Server is a bit sloppy here since it labels the parameter as OUTPUT, but really, this means INPUT/OUTPUT - it basically just means that the stored proc has a chance of returning a value from its call in that parameter.
So yes - even though it's called OUTPUT parameter, it's really more of an INPUT/OUTPUT parameter, and those IN, INOUT, OUT like in Oracle do not exist in SQL Server (in T-SQL).
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.
ERROR: Procedure expects a parameter, but the one it wants is an output parameter?
It's an output parameter. You still have to supply a variable for the value to be written into1:
declare @payroll money
exec spPayroll @payroll OUTPUT
--do something with @payroll
I return the output at the end of the batch
There's no need to do that. And in many cases trying to do so would cause an error. Stored procedures have a return value that must always be an int. That's what the value supplied to return sets. If you're lucky, your supplied value can be converted to int. If you're unlucky, you get a conversion error.
The return value would usually be used if you're using an "error code" convention for indicating errors back to your caller.
1Yes, you have to mark it as OUTPUT when calling too. Omit the OUTPUT here and the code will run but your variable's value will not be set.
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
Error with execute SQL task when using output parameter
This is a data type issue.
Based on the SQL Server official documentation, DT_DBTIMESTAMP data type in SSIS is mapped to the datetime and smalldatetime data types in SQL Server. While, DT_DATE is not mapped to any data type.
To solve this problem, just change the output parameter's data type from DATE to DBTIMESTAMP. Besides, make sure that the User:var_testdt variable is also DateTime.
Stored procedure in SQL Server expecting output parameter as input
A OUTPUT parameter is still an input parameter; there is no such thing as a "non-input" parameter with Stored Procedures. You still need to pass a variable to the parameter, as the value of the OUTPUT parameter will be assigned to that variable once the SP completes.
If you were using T-SQL to execute the SP, it would look like this:
CREATE PROC dbo.MyProc @MyParam int OUTPUT AS
BEGIN
SET @MyParam = 1;
END;
GO
DECLARE @MyParam int;
EXEC dbo.MyProc @MyParam OUTPUT;
PRINT @MyParam; --1
DB<>Fiddle
How to set multiple output parameters in stored procedures
Finally, I was able to achieve the desired result with the below code :
--Creating a table variable
declare @TempTable table(ticketnum varchar(50))
--Inserting values in table variable using procedure
insert @TempTable
select TICKETNUMBER
FROM TicketHistory th
WHERE TICKETTIME IN
(
SELECT MAX(S2.TICKETTIME)
FROM TicketHistory S2
WHERE th.TICKETNUMBER = S2.TICKETNUMBER
)
AND th.CURRENTSTATUS_ANALYST = 'Resolved'
AND th.TICKETTIME < dateadd(day, -5, GETDATE())
--Selecting values from table variable
SELECT * from @TempTable
DECLARE @ticket_number varchar(100)
DECLARE cur CURSOR FOR SELECT ticketnum FROM @TempTable
OPEN cur
FETCH NEXT FROM cur INTO @ticket_number
WHILE @@FETCH_STATUS = 0
BEGIN
insert into TicketHistory (CURRENTSTATUS_ANALYST,TICKETNUMBER,PREVIOUSSTATUS_ANALYST,TICKETTIME,FIELD,CREATEDBY)
values('Closed', @ticket_number, 'Resolved', CURRENT_TIMESTAMP, 'Status', 'User.Auto')
FETCH NEXT FROM cur INTO @ticket_number
END
CLOSE cur
DEALLOCATE cur
END
Related Topics
Join Two Tables Based on Relationship Defined in Third Table
A Procedure to Reverse a String in Pl/Sql
SQL Missing Right Parenthesis on Order by Statement
How to Release Possible Postgres Row Locks
In SQL, Is There Something Like "In", But for Multiple "And" Conditions
Ms Access Query to SQL Server - Distinctrow
MySQL Myisam Table Performance Problem Revisited
Splitting a Comma-Separated Field in Postgresql and Doing a Union All on All the Resulting Tables
Randomly Select a Row with SQL in Access
Prompt for Parameters with SQL Management Studio
Query SQL to Subtract Two Fields
Efficiently Mapping One-To-Many Many-To-Many Database to Struct in Golang
Update Xml Tag in a Clob Column in Oracle
How to Remove Repeated Column Values from Report
Fastest Way to Insert in Parallel to a Single Table
What Is the Most Efficient Way to Write a Select Statement with a "Not In" Subquery