Archive for February, 2010

Cursor in SQL Server 2005

Cursor Declaration


DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,…n ] ] ]


[ LOCAL | GLOBAL ]

This specifies the Scope of Cursors. LOCAL means that this CURSOR is local to Batch, Stored Procedure or Trigger in which it is created and is only valid in within this scope.

GLOBAL specifies that the scope of CURSOR is global to connection and can be referenced in any Stored Procedure, Trigger or Batch executed by this Connection.

However, if neither GLOBAL nor LOCAL is specified, the default will be LOCAL.

[ FORWARD_ONLY | SCROLL ]

It defines the Scroll ability of CURSOR. FORWARD_ONLY cursors can fetch only the next row whereas SCROLL cursors fetch rows in any direction and by any number.

However, FORWARD_ONLY cursors perform faster.

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

STATIC CURSOR has no real time requirement. It just copies Data in Result Set into TEMPDB and fetches row from TEMPDB.

KEYSET CURSOR copies the primary key data into TEMPDB. This is the default CURSOR type. It is more time consuming than DYNAMIC but requires less resources.

DYNAMIC CURSOR also stores keys but the key data is refreshed with each modification to key data on base tables and thus it can keep track of inserted, deleted or modified rows. This is expensive in terms of used resources.

FAST_FORWARD CURSOR only fetches the next row and provides optimal performance.

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

READ ONLY – specifies that data set in cursor cannot be updated.

SCROLL_LOCKS – specifies that cursor will lock the rows to ensure that updates or deletes made through this will be succeeded.

OPTIMISTIC – specifies that cursor does not lock rows and updates or deletes made through this will not succeed if the row has been updated outside this cursor meanwhile.

[ TYPE_WARNING ]

it specifies that client will receive warning message if any implicit conversion occurs in Cursor.

FOR select_statement

This is standard SELECT statement that defines the result set of the cursor.

[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

It defines modifiable columns within the cursor. If this is specified without a column list, all columns can be updated.


@@Fetch_Status

“Fetch” is used to retrieve rows from Cursor. @@Fetch_Status will be equal to zero for a successful return of row. It can have three values.

0 -Row successfully returned.
-1 -Fetch statement has read beyond the number of rows in Cursor.
-2 -Row no longer exists in result set.

Example


DECLARE employeeID int, employeeBalance double;

DECLARE EmployeeBalanceModificationCursor
CURSOR
LOCAL — [ LOCAL | GLOBAL ]
FORWARD_ONLY — [ FORWARD_ONLY | SCROLL ]
KEYSET — [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
— [ STATIC FAST_FORWARD options are incompatible with FOR UPDATE ]
SCROLL_LOCKS — [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
— [ READ_ONLY is incompatible with FOR UPDATE ]
TYPE_WARNING — [ TYPE_WARNING ]
— [ INFORM client of implicit conversions ]
FOR SELECT employee_ID, employee_Balance
FROM HRMDB.t_EmployeeInformation
ORDER BY employee_ID
FOR UPDATE — [ FOR UPDATE [ OF column_name [ ,…n ] ] ]
;

OPEN EmployeeBalanceModificationCursor; –[ OPEN CURSOR ]

FETCH NEXT FROM EmployeeBalanceModificationCursor –[ FETCH First row ]
INTO @employeeID, @employeeBalance ;

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE HRMDB.t_EmployeeInformation — [ Change Balance of current employee ]
SET employeeBalance = @employeeBalance + 5000
WHERE CURRENT OF EmployeeBalanceModificationCursor;

FETCH NEXT FROM EmployeeBalanceModificationCursor –[ FETCH Next row ]
INTO @employeeID, @employeeBalance ;

END

CLOSE EmployeeBalanceModificationCursor; — [ CLOSE Cursor]
DEALLOCATE EmployeeBalanceModificationCursor; –[ DEALLOCATE Cursor]


This is very simple example of Cursor; it is not the correct choice for this kind of scenario. Cursor is only advisable for some dynamic operation that can’t be done with other available alternatives.

Thanks
A Rahim Khan

Using Upsizing Wizard in MS Access 2003 to link tables with MS SQL Server 2005

MS Access is still in use as Database for some its useful features. Few days earlier, I have a situation like I need to develop Reports from Access Database but, truly speaking, I am afraid of writing quires in MS Access, may be, for its limitations. First of all, I think of exporting this database to SQL Server but better alternative is Link Manager or Upsizing Wizard.



1) Click on Upsizing Wizard.



2) Check Create new database.



3) Check Use Trusted Connection. However, if your SQL Database is on other Computer in Network use Login ID and Password to connect Database Server.



4) Move table or tables from Left to Right Pane you want to export.



5) Click Next.



6) Check Link SQL Server tables to existing application and Click Next.



7) Click Finish.



8) You can remove local copy of tables.


Now, MS Access application will work as it was working before linking with MS SQL Server and data will be stored, modified or deleted in SQL Server as well. You can enjoy full-fledged liberty of SQL Server to format data.

Thanks
A Rahim Khan

Padding in SQL Server 2005

SQL Server built-in function RIGHT can be used to format output with leading zeros or other character. It is used with or without REPLICATE function. However, RIGHT function expects string type argument not numeric. If you are in a situation that you need to use numeric value for padding, just convert it to string.

The following are the simplest use of RIGHT with and without REPLCATE.


SELECT RIGHT(‘000000’+ ‘1’, 6)
SELECT RIGHT(REPLICATE(‘000000’,6)+ ‘1’,6)


Here is the output.



This GetApplicantId function generates a formatted ApplicantId using @ApplicationDate, @PositionCode, @ApplicantIdentity.


CREATE FUNCTION GetApplicantId(@ApplicationDate datetime, @PositionCode int,@ApplicantIdentity int )
RETURNS varchar(20)
AS
BEGIN
DECLARE @ApplicantId varchar(20);
SELECT @ApplicantId =
right(‘0000’ + CONVERT(varchar(4),@PositionCode),4) + CONVERT(varchar,@ApplicationDate,112)
SET @ApplicantId = @ApplicantId + ‘-‘
SELECT @ApplicantId = @ApplicantId + right(‘000000’ + CONVERT (varchar(7),@ApplicantIdentity),7)
RETURN @ApplicantId
END


You can use it in query or elsewhere in your application.


SELECT dbo.GetApplicantId(GETDATE(),1,1) as ApplicantId


Thanks
A Rahim Khan

How to write Table Valued User Defined Function in SQL Server 2005

User Defined Function in SQL Server 2005 can be of two types depending on return type; Scalar and Table Valued User Defined Function. Here, I am going to explain how to write the later one.


This udf_CollectExperinceInformation function takes ApplicantID as parameter and returns experience details with Ranking against any specific ApplicantID. Table t_ExperienceType has two fields- ExperienceType_Id and ExperienceType_Name. t_ApplicantExperience table has fields; applicantExperience_ExperienceId, applicantExperience_YearOfExperience, applicantExperience_ApplicantId. This User Defined Function adds ExperienceRanking depending on year of experience.


CREATE FUNCTION udf_CollectExperinceInformation(@ApplicantID int)
RETURNS @ExperienceDetails TABLE
(ExperienceArea varchar(50),YearOfExperience int,ExperienceRanking varchar(2))
As
BEGIN

INSERT @ExperienceDetails(ExperienceArea,YearOfExperience)
SELECT a.ExperienceType_Name, b.applicantExperience_YearOfExperience
FROM t_ExperienceType a,t_ApplicantExperience b
WHERE b.applicantExperience_ExperienceId = a.ExperienceType_Id
AND b.applicantExperience_ApplicantId = @ApplicantID

UPDATE @ExperienceDetails
SET ExperienceRanking = ‘A’
WHERE YearOfExperience > 2

UPDATE @ExperienceDetails
SET ExperienceRanking = ‘B’
WHERE YearOfExperience <= 2
RETURN

END


BINGO!! see how easily you can collect all experience related information using this User Defined Function.


SELECT * FROM dbo.udf_CollectExperinceInformation(1167)


Here is the output.



Thanks
A Rahim Khan

Project Deployment in Visual Studio 2008

Project Deployment is always a critical issue. Those following steps show how easily you can deploy your developed desktop projects. Setup and Deployment project for Desktop project can be of different types; Simple ones are Setup Project and Setup Wizard.



Here, I have chosen Setup Wizard type project to deploy the developed project.



Click Next to continue.



Choose setup type for windows application.



You can add image or text here file if you want.



Click Finish to create the Setup Project.



Select this newly created project, right click on it then click on properties to view the project properties. You can change Icon and title or other properties as marked in image.



Now, add the project you want to deploy as shown in image.



Add Project Output.



Select the project you want to deploy and click OK to continue.



Dependent DLLs or Dot Net Framework will added to Deployment Project.



Right Click on Deployment project and then select view. Clicking File System will bring you here. Now, you can create a folder in program menu and can add project output to it.



Click on User Interface to open the next window.



In User Interface, you can add dialog.



Here, I choose a Customer Informatiom Dialog to add in User Interface.



You can modify Customer Informatiom properties.


Last of all, you need to build your solution. Now, we have our setup file ready in project’s debug folder. However, this is a very simple kind of project deployment in Visual Studio 2008, more appropriately this one will just help to start.

Thanks
A Rahim Khan

Using Try…Catch in SQL Server 2005

Try…Catch is one of the best approach for exception handling and SQL Server 2005 offers it with almost identical syntax as in C#, C++. Try Block contains instructions or SQL statements that may raise an exception whereas Catch Block handles this exception.

Syntax:


BEGIN TRY
{SQL Statements}
END TRY

BEGIN CATCH
{SQL Statements}
END CATCH


Error:

This following code snippet shows how to retrieve error details if any exception occurs.


SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;


Try…Catch Block with SQL Transaction:

Try…Catch Block can be effectively used to deal with COMMIT/ ROLLBACK SQL Transaction. The following Stored Procedure updates t_Project table for any specific valid ProjectID and inserts a row in to t_ProjectHistory table against this row modification in t_Project table. However, you can raise exception of your own for any invalid ProjectID as shown in the code snippet.


CREATE PROCEDURE usp_ModifyProjectComplete (@ProjectID int)
AS
BEGIN TRY
BEGIN TRANSACTION
DECLARE @CountProjectID int

SELECT @CountProjectID = count(project_Id) FROM t_Project
WHERE project_Id = @ProjectID

IF @CountProjectID NOT EQUAL 1 RAISERROR (‘This is not valid Project No’,16,2,@ProjectID);

UPDATE t_Project
SET project_Status = ‘COMPLETE’
WHERE project_Id = @ProjectID;

INSERT INTO t_ProjectHistory (projectHstory_ProjectId, projectHistory_Date,projectHistory_Status)
VALUES (@ProjectID,GETDATE(),’COMPLETE’);

COMMIT
END TRY

BEGIN CATCH
IF @@TRANCOUNT NOT EQUAL 0 ROLLBACK

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH


Last but not the least, you have all the error information available in catch Block so that you can provide treatment or just forward the error to the next level.

Thanks
A Rahim Khan