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

Using Data with ASP.Net – 10 of my ‘Best Practices’

It can hardly be said that any serious programmer has had to deal with database programming at least some time in their careers. So it would be logical then to make sure your code to these underlying databases are as efficient as possible. Hopefully I will share some of the best practices I have learned in dealing with ADO.Net programming. These techniques were learned from a variety of sources, many of them I cannot remember sorry but are public Internet sources. Hopefully you will find them equally as useful as I do.

Best Practice #1

Always use built in .Net data providers.

The built in .Net data providers allow you to take advantage of both the .Net framework and the full power of the database.

Best Practice #2

Always use a config file to store your connection strings. Also it might be a good idea to encrypt these connection strings especially if stored in a dubious location.

It is always best to store data that might change in a location outside of your application where you can easily update the connection strings. Also encrypting the connection strings is always a good idea from a security standpoint.

Best Practice #3

Prefer to use the sorting methods on the SQL Server such as the ORDER BY, HAVING and GROUP BY statements.

By performing the sorting on the server side as opposed to the client side you save time because the server can perform the work faster.

Best Practice #4

You should always try to limit the number of rows in a resultset. This can be performed typically by using the TOP keyword or other similar methods.

By limiting the amount of information you send through the wire you make the application seem faster.

Best Practice #5

It is always best to use CommandBehavior.CloseConnection when you use the ExecuteReader method of a Command object.

This allows for better connection pooling as the connections that are opened are returned quickly.

Best Practice #6

It is always best to cancel before closing a DataReader object if you are finished reading any more rows.

The close method of the DataReader class continues to read all remaining rows before it finally closes the object. This is a wasteful use of resources.

Best Practice #7

It is always best to use a parameterized command (usually a stored procedure) over dynamic SQL queries.

This will improve performance and reduce the chance SQL injection attack while also making your code much more easier to maintain.

Best Practice #8

It is always best to implement some sort of resultset pagination when dealing with results of 50 or more rows.

Although not an easy task in most cases using this technique you can increase performance on both your server database and your client application as less overhead and network traffic is taking place at any one time.

Best Practice #9

It is usually best to implement a timestamp field against all rows – I usually have ‘creationdate’ and ‘lastupdate’ fields.

This will allow you to detect when the database has been updated, and makes it much easier to check your code is interacting with the data source correctly.

Best Practice #10

Don’t return data via ordinals e.g SELECT *

Although a SELECT * may be the quickest way of coding it is certainly the slowest way of returning all the columns – and how often do you actually use all the columns (including datetime stamp / active etc)? Specify the columns you are selecting.