March 23, 2006 at 10:30 am
Hopefully this is something easy for one of you gurus out there. I have the following sample stored procedure, which should return the value of the output parameter:
CREATE PROCEDURE SP_TestOutput
@outparam varchar(20) OUT
AS
SELECT @outparam = Col1 FROM Table1 WHERE ColID = 1)
GO
According to SQL Server Books online, this should return the value of Col1 from Table1 where the ColdID value = 1. Unfortunately I get a "Server: Msg 137, Level 15, State 2, Line 1 Must declare the variable '@outparam'." error message. So naturally I tried adding a declare statement to the SP:
CREATE PROCEDURE SP_TestOutput
@outparam varchar(20) OUT
AS
DECLARE @outparam varchar(20)
SELECT @outparam = Col1 FROM Table1 WHERE ColID = 1)
GO
Now I get an "" error message. Can anyone point me in the right direction?
Thanks,
Bill
March 23, 2006 at 10:54 am
For your error in the first case:
Server: Msg 137, Level 15, State 2, Line 1 Must declare the variable '@outparam'
Did you get this message when creating the procedure, or when running it? The error would be on line 1 if you were able to create the procedure, but then tried the following:
EXEC SP_Output @outparam OUTPUT
Create the procedure with your initial syntax, then try this to test it:
DECLARE @outparam varchar(20)
EXEC SP_Output @outparam OUTPUT
SELECT @outparam AS [OutParam]
-Eddie
Eddie Wuerch
MCM: SQL
March 23, 2006 at 11:27 am
I did get the error message after running the SP.
Running the declare before executing the stored procedure worked in Query Analyzer, but I don't know if that can be done from a .NET application. Why wuld you have to declare a variable outside an SP?
Thanks,
Bill
March 23, 2006 at 9:33 pm
HI,
The output parameters returns value to the calling function or procedure. So you need a variable in the calling function or procedure to catch the returned value.
As when you are calling tyour procedure you must pass one parameter. And obviously you are not passing any constant value. So you need a variable passes as parameter.
And this variable will get value as set after execution of your prcocedure.
Hope it is clear to you now.
March 24, 2006 at 6:01 am
Not exactly clear Ajit. Can you post an example? Maybe that will make it clearer. Thanks!
March 24, 2006 at 8:31 am
Perhaps this will help, as I just worked this out a week ago myself. You have your procedure sp_testoutput with one parameter, @outparam. It's defined INSIDE the procedure when you create the procedure:
CREATE PROCEDURE sp_testoutput --start of procedure, anything below this is invisible outside the procedure
/*DECLARE*/ @outparam /*AS*/ varchar(20) OUTPUT
AS...
However, when you execute your procedure (from EXEC sp_testoutput) you still don't have a destination set up for @outparam, because @outparam only exists inside the stored procedure. You still need to declare a destination variable to store the output of the procedure or it won't have anywhere to go. That's why Eddie's example works... he declares @outparam outside of the sp and tells SQL:
EXEC sp_testoutput /*and catch the output in*/ @outparam OUTPUT
That wouldn't work if he hadn't defined @outparam above, any more than SET @test-2='test' would work without declaring @test-2 beforehand. Does that make any more sense? It may also help to change your variable names to avoid confusion... you could just as easily use:
DECLARE @outputbucket as varchar(20)
EXEC sp_testoutput @outputbucket OUTPUT --again, just says catch the output of the sp in the variable named here
Brian
March 24, 2006 at 11:39 am
> Running the declare before executing the stored procedure worked in Query Analyzer, but I don't know if that can be done from a .NET application.
When you are building your ADO.Net call to the procedure (such as with a SQLCommand object), set the command text to just the procedure name, and then append Parameter objects to the command's Parameters collection.
Each parameter has (at least) the following attributes: Name, Value, Data Type, and Direction. The Direction attribute indicates whether the parameter is Input (you are passing a value in), Output (getting the value out) or InputOutput (both). You can also access the procedure's return value using a Parameter with the ReturnVal Direction To return a value through an OUTPUT parameter of a stored procedure, add a Parameter object to the command using the Output or InputOutput direction codes.
Look up SQLCommand in MSDN, and take a look at the 'parameters' topic, and look up the SQLParameter constructor.
Dim testCommand As SqlCommand = New SqlCommand("SPOutput", connection)
testCommand.CommandType = CommandType.StoredProcedure
Dim myOuputParameter As SqlParameter = testCommand.Parameters.Add( _
"@outparam", SqlDbType.VarChar, 20, ParameterDirection.Output)
'... then fire the call, etc.
'... when the call returns, the myOutputParam object holds the value returned
' from the procedure
Console.WriteLine(myOutputParmeter.Value)
-Eddie
Eddie Wuerch
MCM: SQL
March 27, 2006 at 5:31 am
Thanks Brian and Eddie. I really appreciate the help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply