cursor

It was almost two consecutive weeks of overtime because of so many revisions and additional function in my current project. I always got home so stressed! Now,  I’m almost near the reality.  Hope to finish this until tomorrow.

Here some sample code that makes me feel happy because I learned it from my own.  🙂

set ANSI_NULLS ONset QUOTED_IDENTIFIER ON

GO

–CREATE PROCEDURE [dbo].[sp_SearchName]

ALTER PROCEDURE [dbo].[sp_SearchName]

@strCitizen varchar(200) ,

@strPassportID varchar(50) ,

@strLastName varchar(200) ,

@strFirstName varchar(200) ,

@strMiddleName varchar(200) ,

@strGender varchar(20)

AS

BEGIN

SET NOCOUNT ON;
IF 1=0 BEGIN SET FMTONLY OFF END
IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE NAME LIKE

‘#TempSearchName%’)

BEGIN

BEGIN

TRY

DROP TABLE #TempSearchName END TRY BEGIN CATCH END

CATCH END CREATE TABLE #TempSearchName (

strPassport varchar(50) ,

strLName varchar(200) ,

strFName varchar(200)

,strMName varchar(200) ,

strGender varchar(20) ,

strCitizen varchar(50) ,

strAddKorea varchar(500) ,

strEmail varchar(100)

)

–Cursor

DECLARE @strPassport varchar(50)

DECLARE @strLName varchar(200)

DECLARE @strFName varchar(200)

DECLARE @strMName varchar(200)

DECLARE @sql varchar(MAX)

DECLARE @strPassportCmp varchar(50)

SELECT  @strPassportCmp = ”
DECLARE cursor_Name cursor for SELECT DISTINCT (strPassport)  FROM tbReservation  WHERE nComplete = 1

open cursor_Name fetch cursor_Name into @strPassport

while ( @@fetch_status = 0 )

begin

SELECT @sql = ‘SELECT strPassport, strLastName, strFirstName, strMiddleName, strGender, strCitizen, strAddKorea, strEmail FROM tbReservation WHERE nComplete = 1  AND strPassport = ”’+@strPassport+””

IF (@strCitizen IS NOT NULL AND rTrim(@strCitizen)<>” ) SELECT @sql = @sql + ‘ AND strCitizen = ”’ + @strCitizen + ””

IF (@strPassportID IS NOT NULL AND rTrim(@strPassportID)<>” ) SELECT @sql = @sql + ‘ AND LOWER(strPassport) LIKE LOWER(”’ + @strPassportID + ‘%”)’

IF (@strLastName IS NOT NULL AND rTrim(@strLastName)<>” ) SELECT @sql = @sql + ‘ AND LOWER(strLastName) like LOWER(”’ + @strLastName + ‘%”)’

IF (@strFirstName IS NOT NULL AND rTrim(@strFirstName)<>” ) SELECT @sql = @sql + ‘ AND LOWER(strFirstName) like LOWER(”’ + @strFirstName + ‘%”)’

IF (@strMiddleName IS NOT NULL AND rTrim(@strMiddleName)<>” ) SELECT @sql = @sql + ‘ AND LOWER(strMiddleName) like LOWER(”’ + @strMiddleName + ‘%”)’

IF (@strGender IS NOT NULL AND rTrim(@strGender)<>” ) SELECT @sql = @sql + ‘ AND strGender =”’ + @strGender + ””
INSERT INTO #TempSearchName

EXEC (@sql)
fetch cursor_Name into @strPassport end
close cursor_Name deallocate cursor_Name

Error_Rtn:

SELECT * FROM #TempSearchName SET NOCOUNT OFFEND

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s