How do I use my CURSOR Variable in a WHERE Clause

Question

Is it possible to use a CURSOR Variable in a SELECT WHERE clause or do I have to build dynamic SQL to loop through and plug in the values???

Is this possible or do I need dynamic SQL??

USE	[FBMC]
GO

/*	All Stored Procedure Variables are declared here!												*/

DECLARE	@FirstName							VARCHAR(15),
	@LastName							VARCHAR(20)


DECLARE	Member_Roster_Cursor	CURSOR
GLOBAL
FOR
	SELECT	[MemberRoster].[FirstName],
		[MemberRoster].[LastName]
	FROM	[dbo].[MemberRoster]

OPEN	Member_Roster_Cursor
FETCH NEXT	
	FROM	Member_Roster_Cursor
	INTO	@FirstName,
		@LastName


/* @@FETCH_STATUS being a System Function:
	@@FETCH_STATUS	=	0	indicates a valid FETCH
	@@FETCH_STATUS	=	-1	indicates End of CURSOR
	@@FETCH_STATUS	=	-2	indicates a missing(deleted) row
*/

WHILE	(@@FETCH_STATUS=0)
	BEGIN
		IF EXISTS(SELECT	1
			  FROM		[Report].[dbo].[member]
			  WHERE		[member].[NAME_FIRST]	LIKE	@FirstName		
			  AND		[member].[NAME_FIRST]	LIKE	@LastName		
			BEGIN

Thanks for your review and am hopeful for a reply.....

Answer 1

Yes - that is exactly how you use it.  BOL has examples of cursor usage as well - so please give the documentation a look when you need examples of any particular function or statement.  And 2 final comments.  First, your exists query doesn't seem to be correct, nor will the use of "like" work in the manner you expect (probably).  Second, usage of cursors in efficient and effective tsql is rare.  Usually a set-based approach to achieving your goal is far preferable.

Answer 2

"A set-based approach"....sooooo if I'm trying to Q/A 3rd Party address data with our database, how would you suggest I determine if that Member from our 3rd party is in our database? And I'd like to keep it "LIKE" if at all possible...try and keep it broad.

My thought process was to go through the ~1,000 records using a cursor and SQL with LIKE and then store the Members that are potential matches. I'd like to try and do this as kind of initial scrub of their data to see what I'm dealing with before putting something a little more static together.

Any suggestions are GREATLY appreciated!

Thanks!

Answer 3

That depends greatly on how the information is structured in both databases and how "regular" they are.  This may be a case where a cursor is warranted.  But actually implementing such logic seems to be a rather complex subject and one in which I have no experience.  Perhaps you should give some thought to the responses to your prior question (which I now see) that suggest this is rather complicated. 

I suggest you first start by looking at some examples and finding patterns that you can implement. In other words, stop trying to write code since you don't yet have a handle on the logic needed to do this "matching". 

Answer 4

To be perfectly honest, the best way to make comparisons between addresses is at the post/zip code level. If the two of those match, it's a pretty good bet that they're referencing the same location, even if the street address is spelt differently. For example:

DECLARE @address1 TABLE (address1 VARCHAR(30), city VARCHAR(30), state CHAR(2), zipCode VARCHAR(10))
INSERT INTO @address1 (address1, city, state, zipCode) VALUES 
('123 Any Street WEST','Springfield','PA','12345-6789'), ('123 Some Place','Toronto','ON','T74 7J9')

DECLARE @address2 TABLE (address1 VARCHAR(30), city VARCHAR(30), state CHAR(2), zipCode VARCHAR(10))
INSERT INTO @address2 (address1, city, state, zipCode) VALUES 
('123 Any St W','Springfield','PA','12345-6789'), ('123 Some Pl','Toronto','ON','T74 7J9')

SELECT *
  FROM @address1 a1
    INNER JOIN @address2 a2
	  ON a1.zipCode = a2.zipCode

You can then make comparisons based on the address1 to determine if it is actually the same address. (Does the building number match, for example).

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Answer 5

Use an address scrubbing tool and do not reinvent the wheel. I like Melissa Data, but there are others. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL