Database

The gist: Use scope_identity() rather than @@IDENTITY or you might not get what you're looking for.

"ManyTSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application.

@@Identity is potentially a very, very bad thing! In almost every case, you should use scope_identity() instead.

Why? @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice.

DECLARE @UidAgentGroupID VARCHAR(100)
 
SET @UidAgentGroupID= N'1,2,3,4'
 
DECLARE @sql NVARCHAR(1000)
SET @sql = N'SELECT markup_id FROM Agent_Markup_Fare 
             WHERE UidAgentGroupID IN(?INVARIABLES?)'
 
SET @sql = REPLACE(@sql,'?INVARIABLES?',@UidAgentGroupID)
 
EXEC(@sql)

DECLARE @sqlStatement NVARCHAR(1000)
 
SET @sqlStatement='SELECT AgentsSubGroupID 
                   FROM Agent_Markup_Fare 
                   WHERE AgentsSubGroupID IN(INVARIABLES)'
 
SET @sqlStatement = REPLACE(@sqlStatement,'INVARIABLES',@AgentPreferencesIDS)
 
CREATE TABLE #tmpTbl (AgentsSubGroupId INT NOT NULL)
 
INSERT INTO #tmpTbl
EXEC(@sqlStatement)
 
SELECT 
    @adultmarkup=SUM(dbo.CalculateMarkup(@fareprice,adultMarkup)),
    @childmarkup=SUM(dbo.CalculateMarkup(@fareprice,childMarkup)),
    @infantmarkup=SUM(dbo.CalculateMarkup(@fareprice,infantMarkup)),
    @studentmarkup=SUM(dbo.CalculateMarkup(@fareprice,studentMarkup))
FROM 
    Agent_Markup_Fare 
WHERE 
    fareid=@fareid AND 
    AgentsSubGroupID IN (SELECT AgentsSubGroupID FROM #tmpTbl)
 
DROP TABLE #tmpTbl

DECLARE @Id int;
SELECT @Id = 1;
WHILE @Id IS NOT NULL
BEGIN
    -- Do stuff you need to do here.
 
    -- The next line moves to the next item in the linked list.
    SELECT @Id = NextId FROM MyTable WHERE Id = @Id;
END

Rob Philpott wrote:

Why do you say this isn't really what Sql is designed to do? It seems perfectly acceptable, desirable even (well to me at least!) to have an abstraction where a database can maintain a linked list of data. My operations would be 'return in order', 'delete item in list', 'insert item in list'.

Rob Philpott wrote:

What's the weather like in Scotland? It's been a bit stormy down in London over the past few days.