Call stored procedure with paremeter output in c#

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hi,
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#?

Thanks
 
Paul said:
Hi,
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#?

Thanks
Click to expand...

<http://www.google.com/#hl=en&q=how+...rocedure+ado.net&gs_rfai=&fp=c0cfdbfb1e48170b>
 
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#?
Click to expand...

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);

Arne
 
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#?
Click to expand...

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);
Click to expand...

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
 
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#?
Click to expand...
Click to expand...
Something like (untested):
Click to expand...
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);
Click to expand...

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 -
Click to expand...

Hi,
I don't understant.
What do you menan with SCOPE_INDENTITY() with this example?
Thanks
 
Paul said:
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);
Click to expand...
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 -
Click to expand...

Hi,
I don't understant.
What do you menan with SCOPE_INDENTITY() with this example?
Thanks
Click to expand...

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.
 
On 01-07-2010 11:39, Paul wrote:
I have the next stored procedure in Sql Server 2008:
Click to expand...
CREATE PROCEDURE [dbo].[GetNumTiquet]
@IdEmpresa int,
@NumTiquet int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
Click to expand...
UPDATE ConfGen
SET @NumTiquet=NumTiquet = (SELECT MAX(NumTiquet) +1 FROM ConfGen)
WHERE Id = @IdEmpresa
END
Click to expand...
If i execute this stored procedure with Sql Server 2008 all is ok.
But how can i call this stored procedure with c#?
Click to expand...
Something like (untested):
Click to expand...
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);
Click to expand...

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.
Click to expand...
I don't understant.
What do you menan with SCOPE_INDENTITY() with this example?
Click to expand...

Instead of:

SELECT MAX(id)+1 AS newid FROM table
INSERT INTO table VALUES(newid,val1,val2)
INSERT INTO othertable VALUES(valz,newid)

then if id is a IDENTITY column you can use:

INSERT INTO table(fld1,fld2) VALUES(val1,val2)
INSERT INTO othertable VALUES(valz,SCOPE_IDENTITY())

The problem with SELECT MAX()+1 is that it returns
the correct value at the time of SELECT, but unless
you use transactions and a very high transaction isolation
level, then it may no longer be a correct value when
you use it, because another thread/user/app may have
inserted a new record in between.

Arne
 
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.
Click to expand...
I don't understant.
What do you menan with SCOPE_INDENTITY() with this example?
Click to expand...

Instead of:

SELECT MAX(id)+1 AS newid FROM table
INSERT INTO table VALUES(newid,val1,val2)
INSERT INTO othertable VALUES(valz,newid)

then if id is a IDENTITY column you can use:

INSERT INTO table(fld1,fld2) VALUES(val1,val2)
INSERT INTO othertable VALUES(valz,SCOPE_IDENTITY())

The problem with SELECT MAX()+1 is that it returns
the correct value at the time of SELECT, but unless
you use transactions and a very high transaction isolation
level, then it may no longer be a correct value when
you use it, because another thread/user/app may have
inserted a new record in between.

Arne- Ocultar texto de la cita -

- Mostrar texto de la cita -
Click to expand...

Thanks
 
Back
Top