result from stored procedure

[René de Leeuw]

Hi,

Can someone provide me with code how to send a string (@name) to the test
table. And how to read the result column wich contains the just generated
testid value from the test table. Here details of the table and the stored
procedure. Thanks in advance.

Regards René.

CREATE TABLE [test] (
[testid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE proc ins_test
@name char (10)
AS

SET NOCOUNT ON
BEGIN TRAN

INSERT INTO test
(name)
VALUES (@name)

SELECT @@identity AS result

COMMIT TRAN
SET NOCOUNT OFF
GO

 

[Cowboy \(Gregory A. Beamer\)]

Something like this (note: you can get more explicit, which is a good
thing):

// Connection string, stored proc name and value for parameter
string connString = "{Your connection string here}";
string sprocName = "ins_test";
string nameValue = "value for @name parameter";

// This is used to return the identity value
int IdentityValue;

SqlConnection conn = new SqlConnection(connString);

SqlCommand cmd = new SqlCommand(sprocName, conn);

// Make sure you set command type to sproc
cmd.CommandType = CommandType.StoredProcedure;

// add a new parameter for @name
cmd.Parameters.Add(new SqlParameter("@name", nameValue));

// Error handling for running command
try
{
conn.Open();
IdentityValue = cmd.ExecuteScalar(); //returns only one value
conn.Close();
}
catch(Exception ex)
{
// Code for exception. If not doing anything, do not use catch
}
finally
{
conn.Dispose();
}

You will want to play with this a bit, as it is bare minimum. I did not test
this code, so it may have some typos. The methodology is the same for
VB.NET, so you can step through the idea and code to your language.

One additional note: It is better to use SCOPE_IDENTITY than @@IDENTITY in
most instances. Look up SCOPE_IDENTITY in books online.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[René de Leeuw]

Thanks for your effort. The code is working fine now.

Best regards,
René.

Something like this (note: you can get more explicit, which is a good
thing):

// Connection string, stored proc name and value for parameter
string connString = "{Your connection string here}";
string sprocName = "ins_test";
string nameValue = "value for @name parameter";

// This is used to return the identity value
int IdentityValue;

SqlConnection conn = new SqlConnection(connString);

SqlCommand cmd = new SqlCommand(sprocName, conn);

// Make sure you set command type to sproc
cmd.CommandType = CommandType.StoredProcedure;

// add a new parameter for @name
cmd.Parameters.Add(new SqlParameter("@name", nameValue));

// Error handling for running command
try
{
conn.Open();
IdentityValue = cmd.ExecuteScalar(); //returns only one value
conn.Close();
}
catch(Exception ex)
{
// Code for exception. If not doing anything, do not use catch
}
finally
{
conn.Dispose();
}

You will want to play with this a bit, as it is bare minimum. I did not test
this code, so it may have some typos. The methodology is the same for
VB.NET, so you can step through the idea and code to your language.

One additional note: It is better to use SCOPE_IDENTITY than @@IDENTITY in
most instances. Look up SCOPE_IDENTITY in books online.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

Hi,

Can someone provide me with code how to send a string (@name) to the test
table. And how to read the result column wich contains the just generated
testid value from the test table. Here details of the table and the stored
procedure. Thanks in advance.

Regards René.

CREATE TABLE [test] (
[testid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE proc ins_test
@name char (10)
AS

SET NOCOUNT ON
BEGIN TRAN

INSERT INTO test
(name)
VALUES (@name)

SELECT @@identity AS result

COMMIT TRAN
SET NOCOUNT OFF
GO