How do I use my CURSOR Variable in a WHERE Clause

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.....

March 25th, 2015 10:43am

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.
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2015 11:12am

"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!

March 25th, 2015 12:24pm

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". 

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2015 1:14pm

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).

March 25th, 2015 1:27pm

Use an address scrubbing tool and do not reinvent the wheel. I like Melissa Data, but there are others. 
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2015 11:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics