Use stored procedure output parameter

sql sql-server-2005 tsql stored-procedures output-parameter
shmandor picture shmandor · Aug 5, 2010 · Viewed 7k times · Source 
ALTER PROCEDURE dbo.StoredProcedure8
@emp_code bigint,
@co_id bigint,
@p decimal(8,2) output

AS

SELECT @p = (select sum(tran_value) from emp_ded_ben_trans where emp_code=@emp_code and co_id=@co_id and period_flg=2 and tax_flg=0)

RETURN

Answer

AdaTheDev picture AdaTheDev · Aug 5, 2010

To call that sproc and retrieve the output parameter, you do (e.g.):

DECLARE @p DECIMAL(8,2)
EXECUTE dbo.StoredProcedure8 123, 456, @p OUTPUT
-- @p now contains the output value

Update:

You don't need to use RETURN - you are right in that a RETURN can only return an INTEGER. But a return value is different to an OUTPUT parameter which is what you are actually using.

i.e. to get a RETURN value from a sproc, is different syntax:

DECLARE @Result INTEGER
EXECUTE @Result = SomeSproc

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More