Call stored procedure with paremeter output in c#
in [CSharp]
|
Prev: Are there methods with parameters of interface type in the standard library?
Next: Error: InterfaceDictionary ... does not implement interface member ...
From: Mr. Arnold on 2 Jul 2010 07:14 Paul wrote: > On 1 jul, 23:54, Arne Vajh�j <a...(a)vajhoej.dk> wrote: >> On 01-07-2010 17:52, Arne Vajh j wrote: >> >> >> >> >> >>> On 01-07-2010 11:39, Paul wrote: >>>> I have the next stored procedure in Sql Server 2008: >>>> CREATE PROCEDURE [dbo].[GetNumTiquet] >>>> @IdEmpresa int, >>>> @NumTiquet int OUTPUT >>>> AS >>>> BEGIN >>>> SET NOCOUNT ON; >>>> UPDATE ConfGen >>>> SET @NumTiquet=NumTiquet = (SELECT MAX(NumTiquet) +1 FROM ConfGen) >>>> WHERE Id = @IdEmpresa >>>> END >>>> If i execute this stored procedure with Sql Server 2008 all is ok. >>>> But how can i call this stored procedure with c#? >>> Something like (untested): >>> SqlCommand cmd = new SqlCommand("dbo.GetNumTiquet", con); >>> cmd.CommandType = CommandType.StoredProcedure; >>> SqlParameter prm1 = new SqlParameter("@IdEmpresa", SqlDbType.Int); >>> cmd.Parameters.Add(prm1); >>> SqlParameter prm2 = new SqlParameter("@NumTiquet", SqlDbType.Int); >>> prm2.Direction = ParameterDirection.Output; >>> cmd.Parameters.Add(prm2); >>> cmd.Parameters["@IdEmpresa"].Value = v; >>> cmd.ExecuteNonQuuery(); >>> int res = (int)cmd.Parameters["@NumTiquet"].Value); >> Note that the use of: >> >> SELECT MAX()+1 >> >> usually is the wrong approach and instead SCOPE_IDENTITY() should >> be used in the following SQL statements. >> >> Arne- Ocultar texto de la cita - >> >> - Mostrar texto de la cita - > > Hi, > I don't understant. > What do you menan with SCOPE_INDENTITY() with this example? > Thanks If the key of the record was using Identity Incremental on the primary-key to the record, an int field type, then when a new record is inserted the incremental int key is assigned to the inserted record. The Scope_Identity allows one to get the key of the record inserted at the time of record insertion. This allows you to pass the Identity key to be passed as output from the sproc back to the C# program so it can use in other C# code processing, like retrieve the record by its just inserted record-key ID as an example. |