SP ID Request - 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

SP ID Request

Author

Topic

dannyboy86
Starting Member

8 Posts

Posted - 2007-09-26 : 02:23:22

So i have the following stored procedure that inserts data into a table then returns the table ID...

ALTER PROCEDURE [dbo].[spA_FSH_InsertVslLic1]
@RegistrationDate DATETIME,
@MartimeRegNum INT,
@FishingVesselName VARCHAR(40),
@FishingVesselType INT,
@OperationalStatus VARCHAR(50),
@FishingVesselBasePort INT,
@FishingVesselRemarks VARCHAR(100),
@PreviousAuthorisation VARCHAR(50),
@FishingVesselLenght NUMERIC(18,2),
@FishingVesselWidth NUMERIC(18,2),
@FishingVesselHeight NUMERIC(18,2),
@ConstructionPlace VARCHAR(50),
@ConstructionCountry VARCHAR(25),
@ConstructionYear DATETIME,
@ConstructionShipyard VARCHAR(50),
@ConstructionHullMaterial VARCHAR(50),
@ConstructionRemarks VARCHAR(100)

AS
BEGIN
BEGIN TRY
--insert values into tb_vessellic_vsl_fsh
INSERT INTO tb_vessellic_vsl_fsh
(
vsl_RegistrationDate,
vsl_MartimeRegNumber,
vsl_FishingVesselName,
vsl_vst_VesselTypeID_fk,
vsl_OperationalStatus,
vsl_prt_BasePortID_fk,
vsl_VesselRemarks,
vsl_PreviousAuthorisation,
vsl_OverallLenght,
vsl_Width,
vsl_Height,
vsl_ConstructionPlace,
vsl_ConstructionCountry,
vsl_ConstructionYear,
vsl_ConstructionShipyard,
vsl_ConstructionHullMaterial,
vsl_ConstructionRemarks
)
VALUES
(
@RegistrationDate,
@MartimeRegNum,
@FishingVesselName,
@FishingVesselType,
@OperationalStatus,
@FishingVesselBasePort,
@FishingVesselRemarks,
@PreviousAuthorisation,
@FishingVesselLenght,
@FishingVesselWidth,
@FishingVesselHeight,
@ConstructionPlace,
@ConstructionCountry,
@ConstructionYear,
@ConstructionShipyard,
@ConstructionHullMaterial,
@ConstructionRemarks
)

DECLARE @ID AS INT
SELECT @ID = @@IDENTITY
PRINT @ID
RETURN @ID
END TRY
BEGIN CATCH
EXECUTE spA_GEN_LogError
END CATCH

END

now in the c# code i am calling the stored procedure through this:

public void InsertVslLic1(DateTime regDate, int martimeRegNo, string vesselName, int vesselCategory, string operativeStatus, int basePort, string vesselRemarks,
string previousAuthorisation, double lenght, double width, double height, string constructionPlace, string country, int constructionYear, string shipyard, string hullMaterial,
string structuralRemarks)
{
try
{
//Open Connection
DBConnection db = new DBConnection();
db.OpenConnection();

//Create SQL string
string _sqlString = ("EXECUTE spA_FSH_InsertVslLic1 @RegistrationDate = '" + regDate
+ "', @MartimeRegNum ='" + martimeRegNo
+ "', @FishingVesselName ='" + vesselName
+ "', @FishingVesselType ='" + vesselCategory
+ "', @OperationalStatus ='" + operativeStatus
+ "', @FishingVesselBasePort ='" + basePort
+ "', @FishingVesselRemarks ='" + vesselRemarks
+ "', @PreviousAuthorisation ='" + previousAuthorisation
+ "', @FishingVesselLenght ='" + lenght
+ "', @FishingVesselWidth ='" + width
+ "', @FishingVesselHeight ='" + height
+ "', @ConstructionPlace ='" + constructionPlace
+ "', @ConstructionCountry ='" + country
+ "', @ConstructionYear ='" + constructionYear
+ "', @ConstructionShipyard ='" + shipyard
+ "', @ConstructionHullMaterial ='" + hullMaterial
+ "', @ConstructionRemarks ='" + structuralRemarks + "'");
//Execute SQL String
db.RunSQLQuery(_sqlString);
}
catch (Exception ex)
{
throw ex;
}
}

RunSQLQuery being:

public int RunSQLQuery(string sqlStatement)
{
if (_sqlConnection.State != ConnectionState.Open)
_sqlConnection.Open();

_sqlCommand = new SqlCommand(sqlStatement,_sqlConnection);
return _sqlCommand.ExecuteNonQuery();
}

Now i am stuck on how i will get the ID from the sp (that is @ID) and return it to my C# code as i need it to update the same row further on :)all i am getting till now is the number of rows 'changed' i.e. 1! any thoughts? thanks

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 04:59:00

Couple of things:

You would be better off leaving the RETURN value of the Sproc as the Error state - 0=No error, anything else implies some sort of error.

You can return the IDENTITY instead using either a resultset:

SELECT @ID AS [MyID]

or as an OUTPUT Parameter to the SProc:


ALTER PROCEDURE [dbo].[spA_FSH_InsertVslLic1]
@RegistrationDate DATETIME,
...
@ConstructionRemarks VARCHAR(100),
@ID int = NULL OUTPUT
AS

and you don't then need the DECLARE @ID within your Sproc.

Also, change
SELECT @ID = @@IDENTITY
to
SELECT @ID = SCOPE_IDENTITY()

the use of @@IDENTITY was the "old way", and is subject to unwanted side effects in certain circumstances.

And whilst I'm being pedantic

it would be good to get into the habit of naming the Table/View/Sproc/etc. "owner" as this offers a small performance improvement, and can also help some issues with deployment - well, either way, its a complete bugger if you have to change ALL your code later!

e.g.

INSERT INTO dbo.tb_vessellic_vsl_fsh

Kristen

dannyboy86
Starting Member

8 Posts

Posted - 2007-09-26 : 05:06:47

thanks a bunch for your help, i'm on it right now :)thank you!

dannyboy86
Starting Member

8 Posts

Posted - 2007-09-26 : 06:02:22

it is working perfectly fine thanks...sucks that now i am getting stuck in the c# coding and my RunSQLQuery is not returning the output correctly...its saying that the "_sqlCommand" does not have any parameters..wierd :(

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 06:15:57

Some cache to flush & refresh maybe?

dannyboy86
Starting Member

8 Posts

Posted - 2007-09-26 : 06:32:47

trying...still null :/
i guess the RunSQLQuery method is incorrect

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 07:29:29

Sorry, can't help with the c# .NET stuff, not my field ...