Do I need a Cursor? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Do I need a Cursor?

Author

Topic

jgsteeler
Starting Member

12 Posts

Posted - 2004-02-03 : 09:28:21

I am writing a Report Query using SQL. I have set up a temp table with ID's and Address information . I need to add a column to the Temp table that will have a string with all of the Certifications for each ID in the temp table. The certifications are in anothe table. I declared another table to get all of the certifications for each ID in the Main table. Some of the ID's have more than one Cert. How can I concatanate all of the certs for each ID in to one string and put that string into the main table? I know that I can use cursor but I don't like them and they make the query run slow. I am new to SQL programming so if the solution is simple forgive me. Thanks

jgsteeler
Starting Member

12 Posts

Posted - 2004-02-03 : 09:39:02

Here is the code I am currently using

DECLARE @TEMP TABLe
(FACID int,
FACNAME VARCHAR(75),
CERTTYPE NVARCHAR(4000))
INSERT @TEMP
SELECT intFacilityID,
txtFacilityName,
NUll
from tblproviderFacility FAC

DECLARE FCRS Cursor DYNAMIC
FOR
SELECT FACID
from @TEMP
FOR UPDATE
DECLARE @FID int
OPEN FCRS
FETCH NEXT From FCRS into @FID
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE CERT CURSOR
FOR
SELECT Distinct PCERT.intCertificationID,txtCertificationname,Ct.intCertificationTypeID,txtCertificationTypename
from tblproviderFacility_Certifications PCERT
INNER JOIN tblCertification CRT
on CRT.intCertificationID=PCERT.intCertificationId
INNER JOIN XtblCertificationTypes CT
on Ct.intCertificationTypeID=CRT.intCertificationTypeID
Where intFacilityID=@fid
DECLARE @CID int,
@CNAME VARCHAR(75),
@CTYPE int,
@CTYPENAME VARCHAR(75)
DECLARE @CERT NVARCHAR(4000)
Set @cert=null
OPEN CERT
FETCH NEXT FROM CERT
into @CID,@CNAME,@CTYPE,@CTYPENAME
WHIlE @@FETCH_STATUS=0
BEGIN
IF @CERT IS NOT NULL
BEGIN
SET @CERT=ISNULL(@CERT,'')+SPACE(1)+ISNULL(@CNAME+SPACE(1)+@CTYPENAME,'')
END

IF @CERT IS NULL
BEGIN
SET @CERT=ISNULL(@CERT,'')+ISNULL(@CNAME+SPACE(1)+@CTYPENAME,'')
END

FETCH NEXT FROM CERT into @CID,@CNAME,@CTYPE,@CTYPENAME
END
CLOSE CERT
DEALLOCATE CERT
UPDATE @TEMP
Set CERTTYPE=@CERT
Where CUrrent of FCRS
FETCH NEXT from FCRS into @FID
END
CLOSE FCRS
DEALLOCATE FCRS

SELECT * from @TEMP
where certtype is not null
Order by FACID

Always look on the bright side of life, ta dum, ta dum.

Monthy Python, The Life of Brian