What is the use of a varchar output parameter in a stored procedure - cannot "return @myvarchar" but can "pick @myvarchar"
Xample:
CREATE PROCEDURE dbo.sp_Delete (@p1 INT, @p2 VARCHAR(10) OUTPUT)
AS
--@p1 will have calculations done and get a value for @p2
SET @p2 = 'test'
RETURN @p2
Test:
DECLARE @p2 VARCHAR(100)
EXEC sp_Delete
@p1 = N'1',
@p2 = N''
Mistake:
Conversion failed when converting the varchar value 'test' to data type int.
BUT you can do this:
ALTER PROCEDURE dbo.sp_Delete (@p1 INT)
AS
--@p1 will have calculations done and get a value for @p2
SELECT 'test'
EXEC sp_Delete
@p1 = N'1'
So my question is, what is the use of an OUTPUT parameter of type varchar (note that I did not question the output parameter of type int) when you cannot use "return @myvar" if it is VARCHAR. You can just "choose @myvar". I'm trying to figure out what I'm doing wrong because I think I don't understand the use of a specific varchar variable.
source to share
You are mixing two things, the RETURN value and the OUTPUT value.
RETURN is the "general" execution status of sp, whereas OUTPUT parameters allow you to send multiple values calculated in your procedure.
Also, for the OUTPUT parameters to send back their value, you must specify the OUTPUT keyword
declare @p2 varchar(100)
exec sp_Delete
@p1=N'1',
@p2=@p2 OUTPUT -- first @p2 is then sp_parameter name, second is the @p2 variable from you outer batch
select @p2
You can mix RETURN values with OUTPUT parameters
CREATE PROCEDURE TestProc
@P1 INT,
@P2 VARCHAR(10) OUTPUT
AS
BEGIN
SET @P2='SOME VALUE'
RETURN @P1
END
GO
DECLARE @R INT, @P VARCHAR(10)
EXEC @R=TestProc @P1=1, @P2=@P OUT --OUT is short for OUTPUT, you can use either one
SELECT @R, @P
-----------------
1, 'SOME VALUE'
Without using the OUTPUT key, you will have
DECLARE @R INT, @P VARCHAR(10)
EXEC @R=TestProc @P1=1, @P2=@P -- notice the missing OUTPUT
SELECT @R, @P --was not assigned in this batch so is NULL
-----------------
1, NULL
source to share
The output variable is different from the return value. The return value is always an integer. You can get it like:
exec @retval = dbo.MyStoredProcedure
While the output parameter is being output, for example:
exec dbo.MyStoredProcedure @par_out output
In your case, omit the operator return
. set @par_out = 'value'
enough to return an output parameter to the calling code.
source to share
RETURN
can only return values int
. You don't need to use RETURN
, actually, just omit it:
create PROCEDURE dbo.sp_Delete (
@p1 int,
@p2 varchar(10) OUTPUT
)
AS
--@p1 will have calculations done and get a value for @p2
set @p2 = 'test'
...
declare @p1 int, @p2 varchar(100)
SELECT @p2 = '', @p1 = 1
exec sp_Delete
@p1,
@p2 OUTPUT
select @p2
source to share