Sql procedure is not working

Question

0.00/5 (No votes)

See more:

Dear All

I am stuck in bellow procedure please help me out.

What I have tried:

SQL

DECLARE @msgid AS BIGINT
		DECLARE cur CURSOR LOCAL 
		FOR
		    SELECT msgid
		    FROM   rawtTackHistory_A2Z rn
		    WHERE  rn.msgid > (178370450 )
		    ORDER BY
		           rn.gps_time
		
		OPEN cur
		FETCH NEXT FROM cur INTO @msgid                            
		WHILE @@FETCH_STATUS = 0
		BEGIN
		    PRINT 'in  cursor MsgId :' + (VARCHAR(50), @msgid)
		    
		    FETCH NEXT FROM cur INTO @msgid
		END
		CLOSE cur
		DEALLOCATE cur

above query run fine

SQL

DECLARE @msgid AS BIGINT
		DECLARE cur CURSOR LOCAL 
		FOR
		    SELECT msgid
		    FROM   rawtTackHistory_A2Z rn
		    WHERE  rn.msgid > (SELECT TOP 1 MsgID FROM HistoryMsgID)
		    ORDER BY rn.gps_time
		
		OPEN cur
		FETCH NEXT FROM cur INTO @msgid                            
		WHILE @@FETCH_STATUS = 0
		BEGIN
		    PRINT 'in  cursor MsgId : ' + (VARCHAR(50), @msgid)
		    
		    FETCH NEXT FROM cur INTO @msgid
		END
		CLOSE cur
		DEALLOCATE cur

I replace message id with query then procedure goes unresponsive
SELECT TOP 1 MsgID FROM HistoryMsgID
this query give me same result in 00:00 sec

Posted 21-May-16 4:44am

abdussalam143

Updated 23-May-16 5:58am

v2

Add a Solution

Comments

CHill60 21-May-16 14:07pm

The body of your cursor contains errors. Try running this code outside of a stored procedure and note any error messages. Fix the errors.

abdussalam143 23-May-16 2:47am

it is running fine outside of procedure..

CHill60 23-May-16 5:55am

In that case you have transcribed it into your question incorrectly because the stuff that is in there at the moment will not run at all. Please check the code in your question and correct it by using the Improve question link

abdussalam143 23-May-16 10:24am

please check it now i have update it

ZurdoDev 21-May-16 14:19pm

What is your question? What are we supposed to do with this?

abdussalam143 23-May-16 2:49am

my question is if i pass value in cursor query it work fine but if i pick value from query then it keep executing query for infinite time..

ZurdoDev 23-May-16 6:42am

I don't know what that means. Perhaps you can give some examples. Also, just debug it to find out what is going on.

CHill60 22-May-16 11:12am

Have you resolved your problem?

abdussalam143 23-May-16 2:49am

No Still finding a solution.

Zafar Sultan 23-May-16 10:50am

There is no error in your Cursor. I ran a few tests and everything works fine. The only thing I had to change was the line
PRINT 'in cursor MsgId : ' + (VARCHAR(50), @msgid)
to
PRINT 'in cursor MsgId : ' + CAST(@msgid AS VARCHAR)

ZurdoDev 23-May-16 10:55am

Try declaring a variable and store the message id in the variable before your cursor and then use the variable in the cursor.

2 solutions

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)

CHill60 · Answer 1 · 2016-05-23T05:42:00

As @ZafaSultan has pointed out, there is an error with

SQL

PRINT 'in  cursor MsgId : ' + (VARCHAR(50), @msgid)

Quote:
'VARCHAR' is not a recognized built-in function name.

It should read

SQL

PRINT 'in cursor MsgId : ' + CAST(@msgid AS VARCHAR)

You are saying it is "unresponsive" but you only have PRINT statements in your cursor - remember that the output will appear on the "Messages" tab in SSMS, not in the "Results" tab.

If you want to see the results immediately then use

SQL

RAISERROR('in cursor MsgId : ' + CAST(@msgid AS VARCHAR),0,1) WITH NOWAIT

It won't stop your SP from running (as a normal error would)

Are you sure that

SQL

SELECT TOP 1 MsgID FROM HistoryMsgID

is returning a value <= 178370450 ? You have no WHERE or ORDER BY clause on that query ... perhaps you meant

SQL

SELECT MAX(MsgID) FROM HistoryMsgID

Apart from the above points there is nothing wrong with your code - apart from the fact you are using a CURSOR. As this is all to make the output visible, just use the select to get the results into the Results tab.

Simon_Whale · Answer 2 · 2016-05-23T05:58:00

Personally I would change just a little bit of your code, for Debugging purposes only.

SQL

DECLARE @msgid AS BIGINT
DECLARE @selectMsg AS BIGINT
select top 1 @selectMsg = MsgID FROM HistoryMsgID

--Now Here we can see what @selectMsg is
print @selectMsg

DECLARE cur CURSOR LOCAL 
FOR
    SELECT msgid
    FROM   rawtTackHistory_A2Z rn
    WHERE  rn.msgid > @selectMsg
    ORDER BY rn.gps_time

OPEN cur
FETCH NEXT FROM cur INTO @msgid                            
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'in  cursor MsgId : ' + CONVERT(VARCHAR(50), @msgid)
    
    FETCH NEXT FROM cur INTO @msgid
END
CLOSE cur
DEALLOCATE cur

Sql procedure is not working

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0