MCTS 70-433 calling procedures with output keyword

The exam requires you are aware of the OUTPUT keyword, typically this is used to pass the value of an external stored procedure to an external variable.

The following gives a simple example procedure and some discussions about how to integrate with .NET:

In .net you both Parameter and SQLParameter types have properties for direction.  Each specify 4 types of directions, Input, Output, InputOutput and Return.  I'm writing a wrapper web service for executing stored procedures on our database and I have a method to that retrieves all parameters for a stored proc as well as all the info for each param.  The problem I am having is I can't figure out how do I get an OUTPUT param.  How do you write a stored procedure in which there is an output parameter?  Any time I declare a passed parameter in my stored procedure as OUTPUT .net looks at it as InputOutput.  In the simple example below .net looks at both params as InputOutput....

 

CREATE PROCEDURE [dbo].[SLT_NFLDivisionOUTDemo]

@AFC AS INT = 0 OUTPUT,

@NFC AS INT = 0 OUTPUT

AS

BEGIN

SET @AFC = (SELECT COUNT(*) FROM NFL_DEMO WHERE Division = 'AFC')

SET @NFC = (SELECT COUNT(*) FROM NFL_DEMO WHERE Division = 'NFC')

RETURN

END

1st Answer:

Could you post your .NEt code here. you can do something like this,

SqlParameter para = new SqlParameter("@clientId", client.ClientId);

para.Direction = ParameterDirection.Output;

command.Parameters.Add(para);  // SqlCommand

Hasmukh

2nd Answer:

For SQL Server, stored procedure parameters that are declared with the OUTPUT keyword are actually INOUT. The only parameters you will find in INFORMATION_SCHEMA.PARAMETERS that have PARAMETER_MODE = 'OUT' are name-less parameters that represent the return value of scalar-valued user defined functions.

Also, moving this thread to SQL Server Data Access Forum, where it will have more chances than in the Entity Framework forums.

Thanks,

Diego MSFT

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/bc44e0a5-5dd8-45cb-9c05-76076d5f56f6