Help needed in a stored procedure

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help needed in a stored procedure

Author

Topic

sravz
Starting Member

42 Posts

Posted - 2011-05-03 : 11:27:51

Hi ,

I wrote a Stored procedure like this
CREATE PROCEDURE [dbo].[sp_InsertCompanyRegistration]
@Name varchar(200) ,
@Adress1 varchar(200) ,
@Adress2 varchar(200) ,
@City varchar(200) ,
@State varchar(2) ,
@Zip varchar(20) ,
@Country varchar(3) ,
@COMMENT text=NULL ,
@SchedulerNotesUTS text=NULL ,
@SchedulerNotesClient text=NULL ,
@CompanyType varchar(100)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
IF (NOT EXISTS (SELECT * FROM dbo.CompanyRegistration AS t1
WHERE t1.Name=@Name))
BEGIN
INSERT INTO dbo.CompanyRegistration
(
NAME ,
ADD1 ,
ADD2 ,
CITY ,
STATE1 ,
ZIP ,
COUNTRY ,
COMMENT ,
SCHEDULERNOTESUTS ,
SCHEDULERNOTESCLIENT,
COMPANYTYPE
)
VALUES
(
@Name ,
@Adress1 ,
@Adress2 ,
@City ,
@State ,
@Zip ,
@Country ,
@COMMENT ,
@SchedulerNotesUTS ,
@SchedulerNotesClient,
@CompanyType
)

END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;

END CATCH
END

THIS IS WORKING FINE.

My Question is I want to return the value ID(for the record inserted)---Which is a Identity seed after the insert has been done.
Can any one help me,how to write that.

Thanks,
Sravz

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 11:45:12

SELECT @PARM = SCOPE_IDENTITY()
After the insert. @PARM is an output parameter.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sravz
Starting Member

42 Posts

Posted - 2011-05-03 : 13:15:06

Thanks ......It worked out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources