Call stored procedure with paremeter output in c#

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
 
M

Mr. Arnold

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

<http://www.google.com/#hl=en&q=how+...rocedure+ado.net&gs_rfai=&fp=c0cfdbfb1e48170b>
 
A

Arne Vajhøj

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

Arne
 
A

Arne Vajhøj

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
 
P

Paul

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
 
M

Mr. Arnold

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

Arne Vajhøj

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

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
 
P

Paul

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

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 -

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top