@@IDENTITY Question? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

@@IDENTITY Question?

Author

Topic

losstww
Starting Member

13 Posts

Posted - 2008-07-22 : 01:11:43

The first INSERT Statement adds data to the CaseData Table. The Table Identity Column auto-generates the CaseID number.

I would like the second INSERT statement to add the CaseID number from CaseData into the CaseID Column in the Subject Table. I tried CaseID=(SELECT @@IDENTITY FROM CaseData) but I get an error

How would I write the SQL Statement to accomplish this?

ALTER PROCEDURE dbo.CaseDataInsert

@ReportType varchar(50),
@CreatedBy varchar(50),
@OpenDate smalldatetime,
@Territory varchar(10),
@Region varchar(10),
@StoreNumber varchar(10),
@StoreAddress varchar(200),
@TiplineID varchar(50),
@Status varchar(50),
@CaseType varchar(200),
@Offense varchar(200),
@CaseID int,
@Subject varchar(50),
@LastName varchar(50)

AS

IF NOT EXISTS (SELECT ReportType,CreatedBy,OpenDate,
Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense
FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense)

BEGIN

INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,
Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense)
VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,
@StoreNumber,@StoreAddress,@TiplineID,@Status,@CaseType,@Offense)
End

IF NOT EXISTS (SELECT CaseID, Subject, LastName FROM Subject WHERE CaseID=@CaseID AND Subject=@Subject AND LastName=@LastName)

BEGIN

INSERT Subject(CaseID, Subject, LastName)
VALUES (@CaseID, @Subject, @LastName)

End

I appreciate any help I can get!

Thanks,

Tim

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-07-22 : 01:21:28

The SCOPE_IDENTITY() or @@IDENTITY functions go directly after the INSERT statement. BOL has more detail on these functions.
Here is an example:

SET @CaseID = SCOPE_IDENTITY()

David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 03:32:08

Beware, @@IDENTITY resturns the latest inserted value into the database, regardles of scope, table or user.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 05:22:25

Refer this for understanding difference between them

http://blog.falafel.com/2008/04/23/TSQLTheDifferenceBetweenIdentityScopeidentityAndIdentcurrent.aspx

losstww
Starting Member

13 Posts

Posted - 2008-07-22 : 05:52:10

Thanks - I used SCOPE_IDENTITY and it works. On my web form I have an INSERT button. When the user enters the data and clicks INSERT, the data is inserted into the SQL Database. If the user again clicks the INSERT button, I get a foreign constraint error message.

QUESTION - If I remove the foreign constraint and the user clicks the INSERT button again, the SQL statement's IF NOT EXISTS prevents the data from being entered again.

Is removing the Foreign constraint the best solution, or is there a better method to prevent the error message?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 05:56:06

quote:
Originally posted by losstww

Thanks - I used SCOPE_IDENTITY and it works. On my web form I have an INSERT button. When the user enters the data and clicks INSERT, the data is inserted into the SQL Database. If the user again clicks the INSERT button, I get a foreign constraint error message.

QUESTION - If I remove the foreign constraint and the user clicks the INSERT button again, the SQL statement's IF NOT EXISTS prevents the data from being entered again.

Is removing the Foreign constraint the best solution, or is there a better method to prevent the error message?

what table does foreign constraint relate to?

losstww
Starting Member

13 Posts

Posted - 2008-07-22 : 09:05:11

The foreign constraint relates to CaseData. I removed the foreign key and tried the INSERT and the data entered correctly the first time, however, the second time I clicked INSERT no new data was inserted into the CaseData Table but new data was inserted into the Subject Table. The Case ID was 0.

The format of my Create Procedure is off. The first IF NOT EXISTS statement does not execute the second time around, but the SET @CaseID=SCOPE_IDENTITY is setting a value = 0. When the second IF NOT EXISTS statement runs no CaseID was inserted into the web textbox referenced in the web code so it executes the INSERT INTO statement.

Code is:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Message.Text = ""
If DropDownList1.SelectedValue = "" Or DropDownList2.SelectedValue = "" Or TextBox1.Text = "" _
Or TextBox2.Text = "" Or DropDownList3.SelectedValue = "" Or TextBox3.Text = "" Or _
TextBox4.Text = "" Or DropDownList4.SelectedValue = "" Or DropDownList5.SelectedValue = "" Or _
DropDownList6.SelectedValue = "" Then
Message.Text = "Please Enter Data and click Submit Button."
Else

Dim cs As String = "Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\Database2.mdf;Integrated Security=True;User Instance=True"
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "CaseDataInsert"
cmd.Parameters.AddWithValue("@ReportType",DopDownList1.SelectedValue)
cmd.Parameters.AddWithValue("@CreatedBy",DropDownList2.SelectedValue)
cmd.Parameters.AddWithValue("@OpenDate", TextBox1.Text)
cmd.Parameters.AddWithValue("@Territory", TextBox2.Text)
cmd.Parameters.AddWithValue("@Region", DropDownList3.SelectedValue)
cmd.Parameters.AddWithValue("@StoreNumber", TextBox3.Text)
cmd.Parameters.AddWithValue("@StoreAddress", TextBox4.Text)
cmd.Parameters.AddWithValue("@TiplineID", TextBox5.Text)
cmd.Parameters.AddWithValue("@Status", DropDownList4.SelectedValue)
cmd.Parameters.AddWithValue("@CaseType", DropDownList5.SelectedValue)
cmd.Parameters.AddWithValue("@Offense", DropDownList6.SelectedValue)
cmd.Parameters.AddWithValue("@CaseID", TextBox6.Text)
cmd.Parameters.AddWithValue("@Subject", TextBox7.Text)
cmd.Parameters.AddWithValue("@LastName", TextBox8.Text)

cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()

Message.Text = "Record has been inserted into Database."

End Using
End If
End Sub

I need to figure out how to correct this problem so it runs correctly each and every time.

Tim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 09:52:31

Can you post your store procedure CaseDataInsert?

losstww
Starting Member

13 Posts

Posted - 2008-07-22 : 10:10:54

Stored procedure:

ALTER PROCEDURE dbo.CaseDataInsert

@ReportType varchar(50),
@CreatedBy varchar(50),
@OpenDate smalldatetime,
@Territory varchar(10),
@Region varchar(10),
@StoreNumber varchar(10),
@StoreAddress varchar(200),
@TiplineID varchar(50),
@Status varchar(50),
@CaseType varchar(200),
@Offense varchar(200),
@CaseID int,
@Subject varchar(50),
@LastName varchar(50)

AS

IF NOT EXISTS (SELECT ReportType,CreatedBy,OpenDate,
Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense
FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense)

BEGIN

INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,
Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense)
VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,
@StoreNumber,@StoreAddress,@TiplineID,@Status,@CaseType,@Offense)
SET @CaseID=SCOPE_IDENTITY()
End

IF NOT EXISTS (SELECT CaseID, Subject, LastName FROM Subject WHERE CaseID=@CaseID AND Subject=@Subject AND LastName=@LastName)

BEGIN

INSERT Subject(CaseID, Subject, LastName)
VALUES (@CaseID, @Subject, @LastName)

End

tIM

nirajkeshri2004
Starting Member

2 Posts

Posted - 2008-07-22 : 11:25:54

Hi,
I think this modified versiion of your SP can work.
As i think it will be good to maintain the foreign key constraint.

ALTER PROCEDURE dbo.CaseDataInsert
@ReportType varchar(50),
@CreatedBy varchar(50),
@OpenDate smalldatetime,
@Territory varchar(10),
@Region varchar(10),
@StoreNumber varchar(10),
@StoreAddress varchar(200),
@TiplineID varchar(50),
@Status varchar(50),
@CaseType varchar(200),
@Offense varchar(200),
@CaseID int,
@Subject varchar(50),
@LastName varchar(50)
AS
IF NOT EXISTS (SELECT ReportType,CreatedBy,OpenDate,
Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense
FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense)
BEGIN
INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,
Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense)
VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,
@StoreNumber,@StoreAddress,@TiplineID,@Status,@CaseType,@Offense)
SET @CaseID=SCOPE_IDENTITY
End
ELSE
BEGIN
SELECT @CaseID=CaseId FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense
END
IF NOT EXISTS (SELECT CaseID, Subject, LastName FROM Subject WHERE CaseID=@CaseID AND Subject=@Subject AND LastName=@LastName)
BEGIN
INSERT Subject(CaseID, Subject, LastName)
VALUES (@CaseID, @Subject, @LastName)
End

Niraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 13:24:29

quote:
Originally posted by nirajkeshri2004

Hi,
I think this modified versiion of your SP can work.
As i think it will be good to maintain the foreign key constraint.

ALTER PROCEDURE dbo.CaseDataInsert
@ReportType varchar(50),
@CreatedBy varchar(50),
@OpenDate smalldatetime,
@Territory varchar(10),
@Region varchar(10),
@StoreNumber varchar(10),
@StoreAddress varchar(200),
@TiplineID varchar(50),
@Status varchar(50),
@CaseType varchar(200),
@Offense varchar(200),
@CaseID int,
@Subject varchar(50),
@LastName varchar(50)
AS
IF NOT EXISTS (SELECT ReportType,CreatedBy,OpenDate,
Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense 1 FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense)
BEGIN
INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,
Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense)
VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,
@StoreNumber,@StoreAddress,@TiplineID,@Status,@CaseType,@Offense)
SET @CaseID=SCOPE_IDENTITY
End
ELSE
BEGIN
SELECT @CaseID=CaseId FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense
END
IF NOT EXISTS (SELECT ~~CaseID, Subject, LastName~~ 1 FROM Subject WHERE CaseID=@CaseID AND Subject=@Subject AND LastName=@LastName)
BEGIN
INSERT Subject(CaseID, Subject, LastName)
VALUES (@CaseID, @Subject, @LastName)
End

Niraj

also no need of putting fields inside for exists just put 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources