SQL TSQL Cursor Syntax – A quick reference

I know cursors are big heavy horrid things that make DBA’s shudder and T-SQL gurus cringe. Cursors aren’t usually required, instead some complex TSQL can do the same thing without having to loop through records. But the truth is people use them and so long as some thought is applied to the resource meter things shouldn’t be too bad.

 For the purists and wannabe’s I found this link useful: http://www.sql-server-performance.com/dp_no_cursors.asp

For the rest of us here is a SQL 2000 cursor. If you’re using SQL 2005 have a look here http://msdn2.microsoft.com/en-us/library/ms190028.aspx

DECLARE @price money
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
     SELECT price FROM titles

OPEN @get_price
 
FETCH NEXT FROM @get_price INTO @price

WHILE (@@FETCH_STATUS = 0) 
BEGIN
     IF @Price < 20
 SELECT ‘Under 20′
     ELSE
 SELECT @Price

     FETCH NEXT FROM @get_price INTO @price
END

CLOSE @get_price
DEALLOCATE @get_price

Conditional Select & Casting in T-SQL

Ever wondered how to put a condition on a select and output the detail from a conditional cast?

Here ’stock.Variant1′ and ’stock.Variant2′ are nvarchar fields. I need to select both fields as  decimal(18,2)

ALTER PROCEDURE dbo.FileCounters_GetVariantsInInstance
(
@ProductCode varchar(50),
@Instance int
)
AS

SELECT
stock.[ProductCode],
stock.[Variant1],
stock.[Variant2],
stock.[StockLevel],
CASE IsNumeric(stock.Variant1)
WHEN 1
THEN CAST(stock.Variant1 AS Decimal(18,2))
ELSE 0.0
END AS MyOrder1,
CASE IsNumeric(stock.Variant2)
WHEN 1
THEN CAST(stock.Variant2 As Decimal(18,2))
ELSE 0.0
END AS MyOrder2

FROM
stock
WHERE
stock.ProductCode = @ProductCode
AND
stock.MetaDataInstance = @Instance
ORDER BY
MyOrder1, MyOrder2

RETURN

Using @@IDENTITY vs SCOPE_IDENTITY() and further implications with triggers

They say you learn something new every day – well here’s what I found out today.

I have been guilty of using

select @@IDENTITY

in stored procedures (SQL Server) to return the ID of the record just inserted when the table uses an auto number field as the primary key.

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. Always use SCOPE_IDENTITY() to return the identity of the recently added row.

This has issues when used in conjunction with triggers also. I have included an example below.

Triggers can be a useful mechanism for extending or integrating applications but the use of the above statement causes the original application problems in 2 ways:

1. If the trigger inserts data into other tables the value is altered if the other table also uses auto numbering
2. The original stored procedure starts to return dbNull rather than the ID

Sample Tables

CREATE TABLE dbo.Table1
(
id int NOT NULL IDENTITY (1, 1),
[desc] varchar(255) NULL,
what varchar(255) NULL
)

CREATE TABLE dbo.Table2
(
id int NOT NULL IDENTITY (1, 1),
[desc] varchar(255) NULL,
Extended varchar(255) NULL
)

Sample Stored Procedure

create procedure testing (@Desc varchar(255), @What varchar(255)) as
insert into table1 (desc, what) values (@Desc, @What)
select @@IDENTITY
return

Sample Trigger

CREATE TRIGGER Alert_Me ON [dbo].[tbl_sceneario]
FOR INSERT
AS

DECLARE @Desc varchar(255)

select @Desc = [Desc] from inserted

insert into table2 (desc, extended) values (@Desc, ‘Extended string’)

Problem 1.
@@Identity Changed

Because the trigger inserts into table2 which is using Auto numbered field it will set the @@Identity to its value.

Solution 1

To avoid this you should use SCOPE_IDENTITY()
This will return the last @@Identity generated by the stored procedure and not that from subsequent actions in the transaction

Problem 2.
Value returned is NULL

Due to the way SQL server operates, the trigger seems to affect @@IDENTITY and SCOPE_IDENTITY() and sets the value to NULL when returned as a rowset

Solution 2.

Add an output parameter to the procedure and set its value to SCOPE_IDENTITY

alter procedure testing (@Desc varchar(255), @What varchar(255), @ID int output) as
insert into table1 (desc, what) values (@Desc, @What)
set @ID = SCOPE_IDENTITY
return