Cursor and row retrieval question

[Mark]

Hi everyone, I have a cursor that goes through one table and does a
comparison with another table, code below
==============
CREATE PROCEDURE spMyDataBaseMatch AS
set nocount on
Declare @FirstName varchar(100)
Declare @LastName varchar(100)
Declare @PhoneNumber varchar(100)
Declare @CourseCode varchar(50)
Declare myCursor Cursor FOR
select FirstName, LastName, PhoneNumber from person
Open myCursor
Fetch Next From myCursor INTO @FirstName, @LastName, @PhoneNumber
While @@fetch_status = 0
BEGIN
Select PERLEGALFIRSTNAMETEXT, PERLEGALSURNAMETEXT, OTEINTERNALIDCODE from
MyDataBase6.dbo.vwEnquiriesAllStudents WHERE PERLEGALFIRSTNAMETEXT =
@FirstName AND PERLEGALSURNAMETEXT = @LastName
if @@ROWCOUNT > 0
BEGIN
-----------------------------------------------------
-- I have a question about this next piece
-----------------------------------------------------
SELECT @CourseCode = OTEINTERNALIDCODE FROM
MyDataBase6.dbo.vwEnquiriesAllStudents WHERE PERLEGALFIRSTNAMETEXT =
@FirstName AND PERLEGALSURNAMETEXT = @LastName
-----------------------------------------------------
-- End question
-----------------------------------------------------

insert into MyDataBaseMatch VALUES (@FirstName + ' ' +
@LastName,@CourseCode )
END
Fetch Next From myCursor INTO @FirstName, @LastName, @PhoneNumber
END
deallocate myCursor
GO
=================

Is it possible for me to associate an attribute value in a Select statement?
Like the following

Select @CourseCode = OTEINTERNALIDCODE, PERLEGALFIRSTNAMETEXT,
PERLEGALSURNAMETEXT, from MyDataBase6.dbo.vwEnquiriesAllStudents WHERE
PERLEGALFIRSTNAMETEXT = @FirstName AND PERLEGALSURNAMETEXT = @LastName

instead of what I have written between the tags?
-----------------------------------------------------
--
-----------------------------------------------------

Thanks in advance
Mark

[David Portas]

Always avoid cursors where you can. It looks like your proc can be
re-written as a single INSERT statement that will perform far more
efficiently:

CREATE PROCEDURE spMyDataBaseMatch AS

SET NOCOUNT ON

INSERT INTO MyDataBaseMatch
SELECT firstname + ' ' + lastname, S.oteinternalidcode
FROM Person AS P
JOIN vwEnquiriesAllStudents AS S
ON P.firstname = S.perlegalfirstnametext
AND P.lastname = S.perlegalsurnametext

GO

In answer to the question I think you are asking, variables can be assigned
in a SELECT statement but only if the SELECT doesn't otherwise need to
return any rows to the client. For example, this is valid syntax:

SELECT @CourseCode = oteinternalidcode,
@firstname = perlegalfirstnametext,
@lastname = perlegalsurnametext
FROM vwEnquiriesAllStudents
WHERE ...

but this is not:

SELECT @CourseCode = oteinternalidcode,
perlegalfirstnametext, perlegalsurnametext
FROM vwEnquiriesAllStudents
WHERE ...

Hope this helps.

--
David Portas
SQL Server MVP
--