SQL Return Value

[functionalCode]

I am wondering if anyone can point me in the right direction so I can save the returned identity value from one of my stored procedures. How can I save it into a variable. Any help or examples is appreciated thanks!

[Ramy Mahrous]

If your stored procedure returns value just one value (which returns form aggregate functions) use SqlCommand and give it stored procedure name and then execute it as Scalar; it returns object you then can cast it

Object GetValue (string procName)
{
//I didn't assume stored procedure takes parameters.
SqlConnection conn = new SqlConnection(...);
SqlCommand com = new SqlCommand(procName, conn);
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
}

[functionalCode]

Actually (i failed to mention) I am doing an insert and I need the id of the item added. So I need to save the identity returned from the insert statement.

Thanks

If your stored procedure returns value just one value (which returns form aggregate functions) use SqlCommand and give it stored procedure name and then execute it as Scalar; it returns object you then can cast it

Object GetValue (string procName)
{
//I didn't assume stored procedure takes parameters.
SqlConnection conn = new SqlConnection(...);
SqlCommand com = new SqlCommand(procName, conn);
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
}

[Ramy Mahrous]

I expect you've auto increment identifier

Create proc InsertSP
@somevalues
AS
INSERT INTO YourTable Values(....)
SELECT MAX(PK) FROM yourTable

Then call the InsertSP

btn_click()
{
MessageBox.Show(GetValue("InsertSP").ToString());
}

Object GetValue (string procName)
{
//I didn't assume stored procedure takes parameters.
SqlConnection conn = new SqlConnection(...);
SqlCommand com = new SqlCommand(procName, conn);
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
}

[functionalCode]

Thanks! That should work.

I expect you've auto increment identifier

Create proc InsertSP
@somevalues
AS
INSERT INTO YourTable Values(....)
SELECT MAX(PK) FROM yourTable

Then call the InsertSP

btn_click()
{
MessageBox.Show(GetValue("InsertSP").ToString());
}

Object GetValue (string procName)
{
//I didn't assume stored procedure takes parameters.
SqlConnection conn = new SqlConnection(...);
SqlCommand com = new SqlCommand(procName, conn);
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
}

[Ramy Mahrous]

Please mark it as solved if it's.

[sknake]

Please mark it as solved if it's.

SELECT MAX(PK) FROM yourTable

Using Max(PK) is a dangerous practice to get an identity value :(

Use:

Select Cast(SCOPE_IDENTITY() as int)
--or
Select @@IDENTITY

They are slightly different and you should use SCOPE_IDENTITY() for your purpose.

[Ramy Mahrous]

Yes, Scott I was search about Scope_Identity yesterday I totally forgot it, thanks :)