Passing two output parameters in one stored procedure

• Swapnil S Palande

  Say Hey Kid

  Points: 673

  More actions

  November 20, 2005 at 11:11 pm

  #169786

  Hi All,

  Please help in following query-

  Can I pass two output parameters in one procedure? Please give example.

  I am using vb.net as frontend.

  Thanks.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 21, 2005 at 4:31 am

  #604899

  Sure. You can have as many ouput parameters as you want.

  CREATE PROCEDURE Test

   @Out1 int OUTPUT,

   @Out2 char(1) OUTPUT

  AS

   SELECT @Out1=1, @Out2='Y'

  GO

  DECLARE @Var1 int, @Var2 char(1)

  EXEC Test @Var1 OUTPUT, @Var2 OUTPUT

  SELECT @Var1, @Var2

  --

  I don't know how you would call this from VB though.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Anita K

  Grasshopper

  Points: 24

  More actions

  November 21, 2005 at 5:32 am

  #604904

  In VB6.0 we use the command object and the parameter type as output

  For eg:

  objComd.Parameters.Append objComd.CreateParameter(, adInteger, adParamOutput, 10)

  we can put as many parameters as possible

   

   

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  November 21, 2005 at 6:48 am

  #604914

  In .NET the basics (using Gila's proc)

  Dim param1 As SqlParameter

  Dim param2 As SqlParameter

  Dim CommSQL As New SqlCommand()

  CommSQL.Connection = ConnSQL 'Or whatever your connections is called

  CommSQL.CommandType = CommandType.StoredProcedure

  CommSQL.CommandText = "Test"

  CommSQL.Parameters.Clear()

  param1 = New SqlParameter()

  param1.ParameterName = "@Out1"

  param1.SqlDbType = Int

  param1.Direction = ParameterDirection.Output

  param1.Value = 0

  CommSQL.Parameters.Add(param1)

  param2 = New SqlParameter()

  param2.ParameterName = "@Out2"

  param2.SqlDbType = SqlDbType.Char

  param2.Direction = ParameterDirection.Input

  param2.Value = ""

  CommSQL.Parameters.Add(param2)

  CommSQL.ExecuteNonQuery()

  intOut1 = CType(param1.Value, Integer)

  strOut2 = CType(param2.Value, String)

  you'll need to add declares, connection and error trapping

  Far away is close at hand in the images of elsewhere.
  Anon.