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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Script not working?

Author  Topic 

blakemckenna
Starting Member

1 Post

Posted - 2012-10-05 : 14:37:00
I have a small SQL script that isn't producing the results I'm expecting. I'm not real savvy writing SQL code. If I just run the "Select" statement, it will return 4 rows. However, using the cursor in the way that I am, it's not returning anything. What am I
doing wrong?

The value that is returned is NULL.

Thanks,
Blake


BEGIN
DECLARE @string varchar(25)
DECLARE @output varchar(6000)

DECLARE tmpCur CURSOR STATIC LOCAL
FOR SELECT DISTINCT A.TW_PSuid
FROM RespParty A INNER JOIN Citations B ON B.TW_PSUID = A.TW_PSUID
WHERE RPName Like '%meyers%' And
A.TW_PSuid Is Not Null And
EntityNumber In ('0717','0718');

OPEN tmpCur

WHILE 1 = 1
BEGIN
FETCH tmpCur INTO @string
IF @@fetch_status <> 0
BREAK

SET @output = @output + @string + ',';
END

SELECT @output AS PSUID;

CLOSE tmpCur;
DEALLOCATE tmpCur
END

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-05 : 14:39:31
And why are you wanting to use a cursor here?

-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-06 : 00:25:04
you just need this and no cursor


SELECT STUFF((SELECT DISTINCT ',' + CAST(A.TW_PSuid AS varchar(25))
FROM RespParty A INNER JOIN Citations B ON B.TW_PSUID = A.TW_PSUID
WHERE RPName Like '%meyers%' And
A.TW_PSuid Is Not Null And
EntityNumber In ('0717','0718')
FOR XML PATH('')
),1,1,'')


If you really want to use cursor solution you need to initialise @output to '' or modify code as below

...
SET @output = COALESCE(@output,'') + @string + ',';
..



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -