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

 

[Greg]

do a search on the following datacolumn properties:

autoincrement
autoincrementseed
autoincrementstep

 

[Patrik Löwendahl]

First some comments about your proc,
a) You don't need the explicit transaction, an implicit transaction is
started for you by SQL Server
b) Instead of selecting the value, it's probably more efficent to use an
output parameter.

<t-sql code>
CREATE proc ins_test
@name char (10),
@id int OUTPUT
AS
BEGIN
SET NOCOUNT ON

INSERT INTO test
(name)
VALUES (@name)

IF @@ERROR <> 0
SET @id = @@Identity
ELSE
SET @ID = 0

RETURN 1

SET NOCOUNT OFF
END
GO
</t-sql code>

Now to handle the output parameter in .NET, you use the parameter
collection, I'll use C# for this example:

<csharp code>
// Create connection
SqlConnection sqlCn = new SqlConnection("Data Source=.;Initial
Catalog=db;Integrated Security = SSPI");

// Create command object
SqlCommand cmdPersist = new SqlCommand("ins_test", sqlCn);
cmdPersist.CommandType = CommandType.StoredProcedure;

// Create parameter name object
SqlParameter prmName = cmdPersist.Parameters.Add("@Name",
SqlDbType.Char, 10);
prmName.Value = "Name to add";

// Create ID parameter, mark it as output to recive the id from the proc
SqlParameter prmID = cmdPersist.Parameters.Add("@OutputParm",
SqlDbType.NVarChar, 28)
prmId.Direction = ParameterDirection.Output

// Open the connection and execute the query, expect no rows to be
returned
sqlCn.Open();
cmdPersist.ExecuteNonQuery();
sqlCn.Close(); // Close

// Write out the new id
Console.WriteLine(prmID.Value);
</csharp code>

 

[Gary van der Merwe]

Hi René

This code would work for the stored proc that you have bellow. You would
need to have a using for System.Data and System.Data.SqlClient.


//Create and open Connection

SqlConnection Connection = new SqlConnection("Your Connection String Here");

Connection.Open();

//Create and initilize Values for Command

SqlCommand ins_test = new SqlCommand("ins_test",Connection);

ins_test.CommandType=CommandType.StoredProcedure;

//This Creates the pramaters automaticaly for you. You could do it manualy
by doing calling "ins_test.Parameters.Add("@name",SqlDbType.Char,10);"

SqlCommandBuilder.DeriveParameters(ins_test);

//Fill the pramater vaules.

ins_test.Parameters["@Name"].Value="Gary";

//Execute the command

int NewId = (int)ins_test.ExecuteScalar();


It would be better though to return the @@identy as the result of the SP
like this:

RETURN @@identy

Then you can do a ins_test.ExecuteNonQuery() and get the ID from
ins_test.Parameters["@RETURN_VALUE"].Value

Gary van der Merwe

P.S. before you ask. I'm South Africa, not Dutch.

 

[René de Leeuw]

Thanks all, for your input. The code is working fine now.

Regards René.

 

[William \(Bill\) Vaughn]

And you should avoid use of @@Identity. While it works in simple cases, it
will fail in more complex scenarios. Use SCOPE_IDENTITY() instead.
See my article(s) on handling parameters and identity values
(http://www.betav.com/msdn_magazine.htm)

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi René

This code would work for the stored proc that you have bellow. You would
need to have a using for System.Data and System.Data.SqlClient.


//Create and open Connection

SqlConnection Connection = new SqlConnection("Your Connection String Here");

Connection.Open();

//Create and initilize Values for Command

SqlCommand ins_test = new SqlCommand("ins_test",Connection);

ins_test.CommandType=CommandType.StoredProcedure;

//This Creates the pramaters automaticaly for you. You could do it manualy
by doing calling "ins_test.Parameters.Add("@name",SqlDbType.Char,10);"

SqlCommandBuilder.DeriveParameters(ins_test);

//Fill the pramater vaules.

ins_test.Parameters["@Name"].Value="Gary";

//Execute the command

int NewId = (int)ins_test.ExecuteScalar();


It would be better though to return the @@identy as the result of the SP
like this:

RETURN @@identy

Then you can do a ins_test.ExecuteNonQuery() and get the ID from
ins_test.Parameters["@RETURN_VALUE"].Value

Gary van der Merwe

P.S. before you ask. I'm South Africa, not Dutch.

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