Urgent - return values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Urgent - return values

Author

Topic

lorenb
Starting Member

7 Posts

Posted - 2008-09-17 : 19:35:53

Hi,
I'm a newbie to SQL server though I have been running some queries successfully.
I have a table named "employees" which has the following 3 columns:
"ID", "emp_fname", "emp_lname". The ID is a PK and serves as a unique field.
I have a stored procedure which add employees to the table - this works fine, however, I would like the stored procedure to return the ID of the new employee just added. How to do it?

Here is my stored procedure:

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddEmp]
@emp_fname nvarchar(50),
@emp_lname nvarchar(50)
AS

BEGIN
SET NOCOUNT ON;
INSERT INTO employees(emp_fname, emp_lname)
VALUES (@emp_fname, @emp_lname)

END

Thanks

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-17 : 19:38:48

[code]declare @return int

INSERT INTO employees(emp_fname, emp_lname)
VALUES (@emp_fname, @emp_lname)

set @return = scope_identity()
return @return
[/code]

lorenb
Starting Member

7 Posts

Posted - 2008-09-17 : 19:44:21

Thank you very much, Sir!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-17 : 19:44:58

Glad2help

lorenb
Starting Member

7 Posts

Posted - 2008-09-17 : 20:38:00

I got happy too fast :)
In the VS2008, I have added this modified existing stored procedure via the designer, run the wizard and choose to return a "single value" and run it via the designer - it indeed return the integer I need. However, when I run it by calling the function, I get always "0"

public static int AddEmp(string fName, string lname)
{
int newID = employeeTableAdapter.AddEmployee (fName, lname);
return newID;
}

lorenb
Starting Member

7 Posts

Posted - 2008-09-17 : 20:42:49

as it returns an object, correct function I run is
public static int AddEmp(string fName, string lname)
{
int newID = Convert.ToInt32(employeeTableAdapter.AddEmployee (fName, lname));
return newID;
}

lorenb
Starting Member

7 Posts

Posted - 2008-09-18 : 01:10:51

What am I doing wrong? is the convert problematic? I still get always 0 as a return value while running this function.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-18 : 02:48:53

This is a .net problem not ms sql. Next time post it in the .net forum

from your code, its returning newID to the .net code.

What I believe you want, is to return the return value from the SP ???

I dont see your database code. Can you explain

lorenb
Starting Member

7 Posts

Posted - 2008-09-18 : 12:09:26

Thanks for replying. Next time, I'll submit in the .Net forum.
I would like to return the value of the new added employee to the .Net

Here is what I did:
1) created a stored procedure as stated above
2) using the designer, I have created a dataset and data table.
3) I have exposed the stored procedure in a DAL (below)
4) From a winform applicaiton, I call this function int the DAL
5) Observation - it keep giving 0 as a return value though running the stored procedue in the VS designer give proper value...

static class DAL
{
private static DS.employeesDataTable empDS = new DS.employeesDataTable();
private static employeesTableAdapter empTA = new employeesTableAdapter();

public static int AddEmp(string fname, string lname)
{
return Convert.ToInt32(empTA.AddEmp(fname, lname));
}
}

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
int newEmpID = DAL.AddEmp("John", "Smith");
}
}

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-18 : 13:36:25

am confused

Sorry am not into winforms, more of asp.net web apps, but they much different.

where does your code call the SP and pass the parameters. I only see

{
return Convert.ToInt32(empTA.AddEmp(fname, lname));
}

This code, which i pasted above, will return the @return variable to your calling code.


declare @return int

INSERT INTO employees(emp_fname, emp_lname)
VALUES (@emp_fname, @emp_lname)

set @return = scope_identity()
return @return

lorenb
Starting Member

7 Posts

Posted - 2008-09-18 : 13:50:55

Thanks, I appriciate your effort!
My code calls the SP by using the table adapter (please refer to the DAL class: "empTA.AddEmp(fname, lname)". the SP is called "AddEmp"

Here is my stored procedure code (I copy-paste it fromt he DB):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddEmp]
-- Add the parameters for the stored procedure here
@emp_fname nvarchar(50),
@emp_lname nvarchar(50)
AS
BEGIN

declare @return int

INSERT INTO employees(emp_fname, emp_lname)
VALUES (@emp_fname, @emp_lname)

set @return = scope_identity()
return @return

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources