.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Stored Procedure, when to use Output paramemter vs Return variable

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

When would you use an output parameter vs a return variable, or vice versa? In the following simple example, I can achieve the samething using either one.

Using output parameter

create proc dbo.TestOutput (@InValue int, @OutValue int output)
as
set @OutValue = @InValue

declare @x int
exec TestOutput @InValue = 3, @OutValue = @x output
select @x

 

Using return variable:

create proc dbo.TestReturn (@InValue int)
as
return @InValue

declare @x int
exec @x = TestReturn @InValue = 3
select @x

 

As you can see, they both do the samething. Can someone show me an example where the choice of a output parameter vs a return variable would make a difference?




View Complete Post


More Related Resource Links

Can I pass a value for a output variable in a stored procedure in ADO.NET?

  
I am calling a stored procedure, that exists in a SQL Server 2005 database,  using ADO.NET, in which I instantiate a sql command object.The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in  a stored procedure?

How do you set SSIS package variable equal to an output parameter from a stored procedure that is ty

  

I have a stored procedure insertXXX that has one OUTPUT parameter of type numeric. The stored procedure looks like:

ALTER

 

PROCEDURE [dbo].[insertIntoTable]
@outputID
AS numeric OUTPUT

AS

BEGIN

 

optional output parameter in CLR Stored Procedure

  
I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it. the declaration goes as follow   <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/> Public Shared Sub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> Optional ByVal De As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Para As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Copia As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Oculta As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Assunto As String = "", <SqlFacet(MaxSize:=-1)> Optional ByVal Mensagem As String = "", _<br/> <SqlFacet(MaxSize:=-1)> Optional ByVal Anexo As String = "", _<br/> <strong><SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""</strong> , _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Smtp As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Usuario As String = "",

SQL Server 2008 Stored Procedure output parameter

  
I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

Stored Procedure utilizing table variable

  
I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

Stored Procedure Default Return Value

  
Tried searching and have not found the answer to this one.  Think I know from testing but want to be sure.   I have a SP on  MSSQL 2000 that I created.  Within the table I call 6 update statements.  All my params are input. I developing software using VS 2008 PRO with a typed dataset.  I added the stored procedure to my tableadapter and it created the subsequent update() method with an int return type.  I coded a statement to test the return value like so... int Success = tableadapter.update(value1,value2,etc) I examined the Success variable and found it to be 6. I looked at another SP that calls 7 update statements within it and the return value after being called was 7. I did not specify any return value in my SP.  I can only presume that the SP is by default returning the aggregate amount of rows affected.  My question is if my presumption is correct.  My reason for asking is I would like to know this value is reliable for testing that all 6 update statements in the SP were  successful.    Thank you.

Passing Table Variable to Stored Procedure

  
My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

How to get the return value from stored procedure

  
Hi,I  not getting the return value from stored procedure that I have written so how to get that plzz its urgent I have return the procedure as ALTER PROCEDURE [dbo].[sp_AddPaySlip]  -- Add the parameters for the stored procedure here @inDMLType varchar(1), @EmpID varchar(2000) = '', @BankAccountNumber nvarchar(50) = '', @NoOfDaysWorked varchar(500)='', @GrossSalary varchar(2000) = '', @Basic varchar(500) = '', @PerformanceAllowance numeric=0, @PersonalAllowance numeric=0, @Hra numeric=0, @PF numeric=0, @PTax varchar(10)='', @Tds numeric=0 , @Total varchar(500) = '', @NetPay varchar(500) = '', @PFAccountNumber varchar(100)='', @Year varchar(10)='', @Month varchar(10)='', @ou_intRetValue int=0 output AS BEGIN declare @l_sqlqry nvarchar(1000) ,@intError INT ,@inCnt int --set @l_sqlqry ='' --set @interror = 0-- Set @inCnt =0 --If @inDMLType  not in ('I') goto Insufficient_Data  --IF @inDMLType  ='I' -- for Inserting  DECLARE @result int BEGIN /*if exists(Select 1 from users where Year(DateofJoin) >= @Year and EmpID = @EmpID) Begin set @interror = 2 End if exists(Select 1 from users where month(DateofJoin) > @Month and EmpID = @EmpID) Begin -

No Return Type for a Stored Procedure in a Linq to SQL Data Layer

  
Hello. First, I don't know if this is the right forum section to post this in, but I couldn't fine a more suitable one. Feel free to move this if it's totally worng. I have a weird problem when using LINQ to SQL and Store Procedures with a return value... The problem is that when I drag my SP's onto the "Methods Pane" the property field "Return Type" is set to (None) and greyed out. And here's the most weird thing... I created a test SP yesterday wich returned SCOPE_IDENTITY() and it worked flawlessly. Dragged it onto the "Methods Pane", could play with the return type property (hence it was not greyed out) and everything was working out nice. I used a ISingleResult<T> and could fetch the .ReturnValue... Today I was going to contiune to create the rest of the SP's i needed with a return type but today when I drag my Store Procedures to my Methods Pane, I couldn't access the Return Type property... I even dragged the SAME procedure I used yesterday (wich then worked great) and now I can't get the dbml-layer to notice that there is a return value... I've tried to create new solutions, new Data Layers, used different databases/servers, but still the same problem... Don't really know if it's LINQ or something else that's causing this... This is how part of my SP wich I used yesterday to test looks

Incorrect output from Stored Procedure

  

Hello! I am trying to get a stored procedure to show me an email address in the EmailAddress column of the table based off of the input, which in this case is the CustomerID. So basically, say 3 is the CustomerID I want the sproc to show the email address for customer id 3, but instead it's just showing me the EmailAddress column heading. Here is my code below, help!

Thanks

DROP PROCEDURE Email
GO
CREATE PROCEDURE Email(@cid INT, @email [VARCHAR] = OUT)
AS
SELECT * FROM SalesLT.Customer
WHERE @email = EmailAddress AND @cid = CustomerID
PRINT @email


How to return looped procedure results so that they display as multiple rows of a single output inst

  

I understand that the below fragment will return a separate 1-row results set for each successful loop (also unnecessarily repeating the column header each time). How can I alter my procedure so that each value returned by the loop is appended underneath the previous value as multiple rows of a single results set? Do I have to store each return value in a temp table or someother storage object? It would be nice to just be able to spit them out row by row as each loop returned. Possible? Easy? Hard? I really appreciate the help I've received here so far.

DECLARE techCursor CURSOR 
FOR 
SELECT (whatever)

OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE pr_FindExpenses4Tech @techNumLoop, @itemCount OUTPUT
SELECT @itemCount Item_Count
F

How to get the return value from a stored procedure

  

Hi everyone..

I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

this is my procedure..

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/******************************************
Check if the PO has been invoiced or paid
************************

How to get the return value from a stored procedure

  

Hi everyone..

I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

this is my procedure..

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/******************************************
Check if the PO has been invoiced or paid
************************

Send NULL value to a stored procedure variable

  

Hi all. I want to send an image to a stored procedure variable that is varbinary(max) field, It works correctly when an image selected by fileupload and my image property has image file, and it can send to stored procedure. but when fileupload is empty I want to send NULL to stored procedure variable that is varbinary(max). I use DbNull.Value in C# code. But error occurs. What should I do for send Null to stored procedure? Cry


How to get the return value from a stored procedure

  

Hi everyone..

I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

this is my procedure..

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/******************************************
Check if the PO has been invoiced or paid
************************

Need a string output from stored procedure

  

hai everyone,,

I got a situation now... i had a stored proc: How can i get the final select statement in the code behind.


Here is the stored proc.

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/**********************

Use a stored procedure to return data for algorithm

  

Hi,

I am looking at using the time series algorithm to predict a products future.

However, I seem to have missed the obvious. How do I return a subset of a tables data as the input to the algorithm?

Can I specifiy a SP to use or do I specify the parameters within DMX?

 

Thanks


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend