why my cursor query return 0 value

Question

4.50/5 (2 votes)

See more:

SQL-Server-2008

Why does my stored procedure return 0 value instead of goods1,goods2,?

SQL

DECLARE @vendor_name nvarchar(50),@message varchar(80);

    
DECLARE vend_cursor CURSOR
    FOR SELECT DISTINCT dbo._Goods.god_Name
     FROM dbo._Roshani INNER JOIN
                     dbo._RoshaniGoods ON dbo._Roshani.rsh_id = dbo._RoshaniGoods.rsg_rshId INNER JOIN
                   dbo._Goods ON dbo._RoshaniGoods.rsg_godId = dbo._Goods.god_Id
                      where rsh_rgiId=@rgi_Id
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
INTO  @vendor_name;

WHILE @@FETCH_STATUS=0
BEGIN
    --PRINT ' ';
    SELECT @message = ', ' + @vendor_name;
FETCH NEXT FROM vend_cursor
INTO  @vendor_name;
    
end
PRINT @message;
CLOSE vend_cursor;
DEALLOCATE vend_cursor;

Posted 5-Jun-11 21:06pm

faezeh66

Updated 5-Jun-11 22:22pm

v7

Add a Solution

Comments

Dalek Dave 6-Jun-11 3:35am

Edited for Grammar.

3 solutions

Solution 1

after the print operation you need the fetch the next value. Since you are fetching once only, value of "@@FETCH_STATUS" is remaining <> 0 and hence the infinite loop.

Posted 5-Jun-11 21:20pm

RakeshMeena

Comments

faezeh66 6-Jun-11 3:29am

give me query plz

Dalek Dave 6-Jun-11 3:36am

Good Call.

Solution 3

DECLARE @vendor_name varchar(50),@message varchar(80);
DECLARE vend_cursor CURSOR
FOR SELECT DISTINCT dbo._Goods.god_Name
FROM dbo._Roshani INNER JOIN
dbo._RoshaniGoods ON dbo._Roshani.rsh_id = dbo._RoshaniGoods.rsg_rshId INNER JOIN
dbo._Goods ON dbo._RoshaniGoods.rsg_godId = dbo._Goods.god_Id
where rsh_rgiId=@rgi_Id 
And isnull(dbo._Goods.god_Name,"0") <>  "0" -- You will need this line
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
INTO @vendor_name;
 <code></code>
	WHILE @@FETCH_STATUS=0
	BEGIN
			--PRINT ' ';
			SELECT @vendor_name; ----- Products From Vendor:  @vendor_name;
			 
	FETCH NEXT FROM vend_cursor INTO @vendor_name; -- You Forgot this line
	END
PRINT @message;
CLOSE vend_cursor;
DEALLOCATE vend_cursor;<pre><pre>

Posted 5-Jun-11 21:26pm

cuteband

Updated 5-Jun-11 22:04pm

v6

Comments

faezeh66 6-Jun-11 3:45am

thank you, but it get me
goods1
goods2
0
but i want goods1,goods2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prerak Patel · Accepted Answer · 2011-06-05T21:25:00

Solution 2

Check the result of the query first

SELECT DISTINCT dbo._Goods.god_Name
FROM dbo._Roshani 
  INNER JOIN dbo._RoshaniGoods ON dbo._Roshani.rsh_id = dbo._RoshaniGoods.rsg_rshId 
  INNER JOIN dbo._Goods ON dbo._RoshaniGoods.rsg_godId = dbo._Goods.god_Id
WHERE rsh_rgiId=@rgi_Id

Does it return 2 values?

Edit:

SQL

SET @message = @message + ',' + @vendor_name

Posted 5-Jun-11 21:25pm

Prerak Patel

Updated 5-Jun-11 22:11pm

v2

Comments

faezeh66 6-Jun-11 3:26am

yes

Prerak Patel 6-Jun-11 3:31am

After adding FETCH NEXT FROM vend_cursor INTO @vendor_name; in loop, did it work?

faezeh66 6-Jun-11 4:07am

no but i get result
goods1
goods2

but i want goods1,goods2,...

Prerak Patel 6-Jun-11 4:11am

Try using SET @message = @message + ',' + @vendor_name

faezeh66 6-Jun-11 4:22am

i use it but return null

Prerak Patel 6-Jun-11 4:28am

Try SET @message = '' after declaring it.

faezeh66 6-Jun-11 5:50am

thank you very much,it is solved,but i cant return it?can i return varchar(40) from store procedure?

Prerak Patel 6-Jun-11 5:53am

Check this to see how to return and get values from SP.
http://www.sqlteam.com/article/stored-procedures-returning-data

You may need to specify OUTPUT parameter.

faezeh66 6-Jun-11 6:17am

it is solved ,thank you for your attention

Prerak Patel 6-Jun-11 6:18am

You are welcome. :)

Prerak Patel 6-Jun-11 4:07am

and are you sure about use of +=?

faezeh66 6-Jun-11 4:23am

i use
SET @message = @message + ',' + @vendor_name
but return null

Dalek Dave 6-Jun-11 3:36am

Good Answer.