HOW TO DEBUG A STORED PROCEDURE IN SQL SERVER 2005

My manager has told me this when i am facing some issues in a stored
procedure on a DEMO day.I would like to thank him for the same.

Go to Visual Studio –> Open SERVER Explorer –> Add Data
Connection –> SELECT you data base –> Right Click your SP
–> Select STEP IN Stored Procedure.
Use F10 to degug.

Share Knowledge.We all are here to learn.

Pawan Kumar

Pawankkmr@hotmail.com

CLUSTURED INDEX AND NON CLUSTURED INDEX DIFFERENCES IN SQL SERVER 2005

1. Data is physically sorted in clustured indexes and in case of non clustured indexes data is logically sorted.

2. We can create maximum of 1 clustured index on a table in sql
server 2005. In case of non clustured index we can create maximum of
249 indexes on a table in sql server 2005.

3. The leaf page of the clustured index contains entire data. The
leaf page of non clustured index does not contain entire data. It will
provide book mark look up.( FILE NO | Page NO | ROW NO )

Share Knowledge.We all are here to learn.

Pawan Kumar

Pawankkmr@hotmail.com

USE OF NULLIF IN SQL SERVER 2005

Syntax for NULLIF is NULLIF (expression ,expression )

NOTES about return type:
a) NULLIF returns the first expression if the two expressions are not equal.
b) If the expressions are equal, NULLIF returns a null value of the type of the first expression.

1. SELECT NULLIF(1,1) AS ‘Output’ — if both the expressions are equal then it will return NULL

OUTPUT
________________

NULL

2. SELECT NULLIF(9,1) AS ‘Output’ — if both the expressions are not equal then it will first expression as output

OUTPUT
______________

9

3. SELECT NULLIF(NULL,9) AS ‘Output’ — This will give error if the first expression is NULL

OUTPUT
______________

Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.

4. SELECT NULLIF(9,NULL) AS ‘Output’ — This will give output as 9
as both expressions are not equal and first expression is not null.

OUTPUT
______________

9

Happy Programming.

Pawan Kumar
Pawankkmr@hotmail.com

ADD N NUMBER OF DAYS IN CURRENT DAYS IN SQL SERVER 2005

–1.You can directly add the no of days you want to add in the date.Eg.

DECLARE @DATE AS DATETIME

SET @DATE = GETDATE()

SELECT @DATE + 5

Output
___________________

2010-08-10 14:46:08.137

–2.You can use DateAdd function to add the no of days in the date.Eg.

DECLARE @DATE AS DATETIME

SET @DATE = GETDATE()

SELECT DATEADD(d , 5 , getdate())

OUTPUT
__________________

2010-08-10 14:47:22.843

_____________________________________________________

Happy Programming.

Pawan Kumar
Pawankkmr@hotmail.com

HOW TO REMOVE DATE FROM DATETIME FIELD IN SQL SERVER — USE CONVERT

SELECT CONVERT ( VARCHAR(10) , GETDATE() , 108 ) AS ‘TIME’

OUTPUT
__________________

14:39:32

Happy Programming.

Pawan Kumar
Pawankkmr@hotmail.com