Cursor with Stored Procedure Question

Question

Hi everyone, this is my first time using this site.

OK, i need to use a cursor to call a stored procedure that has 2 parameters that i need to pass into from Customers table.

Here is what i mean;

My goal is to pass all the CustomerID and CustomerName from Customers table into my stored procedure called AddCustomers which has 2 parameters of CustomerID and CustomerName. ie: AddCustomer [CustomerID],[CustomerName] [side note: this AddCustomer stored procedure does some filtering things that i need for my apps]

So the end result of using this CURSOR with an stored procedure is to DUMP/PASS all of customerID and CustomerName from Customers table into AddCustomer stored procedure.

I have used sp_executesql, but not successfully.

Here is what i have tried but not working.

Declare @CustomerID int
Declare @CustomerName varchar(100)

Declare cur CURSOR READ_ONLY
FOR
SELECT CustomerID, CustomerName
from Customers

OPEN cur

FETCH NEXT FROM cur
INTO @CustomerID, @CustomerName

WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC AddCustomer @CustomerID, @CustomerName 
   FETCH NEXT FROM cur
   INTO @CustomerID, @CustomerName
END

CLOSE cur
DEALLOCATE cur

---

I am new to the use of cursors so I am not sure why this isn't working. If you can provide any clues or links, I'd would appreciate it.

Answer 1

Why are you using a cursor? Do you realize that's the least efficient, most complex way to approach this problem? There's a much better than even chance that you'd be better off with a modified version of your stored procedure. Or one of several other patterns.

---

Again, the simplest approach would probably be to modify the stored procedure (making a new one, if you want) for input.

Or, you could use your language of choice C#, VB, whatever) to query for the customers, and loop there on the stored procedure.

How do you intend to handle exceptions otherwise/

Answer 2

Your cursor is fine from syntax perspective. I'm guessing that the issue has something to do with your stored procedure throwing an error. You should post the code for the stored procedure as well.

And the error message(s).

Answer 3

OK, if Cursor CANNOT loop thru stored procedure, THEN can anyone provide a solution or hints to my problem, which is to PASS all CustomerID and CustomerName from Customers table into my AddCustomer stored procedure so that i can do some filtering stuff on each of the CustomerID and CustomerName that i pass into AddCustomer Stored procedure.

thanks in advance.

Answer 4

Sam,

There are times when using a stored procedure to do a task like this is perfectly appropriate. Le Dorfier has certainly given me lots of good advice but I have to disagree with him on this one. Especially if this is a one-time processing of your records to achieve some form of transformation, then you are really close to your solution as I don't see any obvious flaws in your code. BTW, I've taken the same approach on more than one occasion and it has worked very well.

So...post the stored procedure as well as we'll see if that doesn't help. Also, what specific error are you seeing?

UPDATE: Wait, are you just running this in the SQL Window? I usually package this stuff up in another stored procedure (often just a temporary one) and then run it. Of course, it may also be that you just need to execute this code - not using EXEC but just using the Execute button...it is hard to tell based on your description.

UPDATE 2: You say above that the error is something about the table. Are you sure that the data types that you have defined are correct? Could the error be a mismatch between the data pulled from the table and the type of the var into which you are placing it?

Answer 5

There doesn't seems to be anything wrong with the syntax. We really need the error messages in order to provide an answer.

Being blind my best shot would be the varchar(100) ... perhaps the table has it nvarchar ...

There doesn't seems to be anything wrong with the syntax. We really need the error messages in order to provide an answer.

Being blind my best shot would be the varchar(100) ... perhaps the table has it nvarchar ...

Update 1: k, you mentioned on a comment

"The data type should be the same. I have taken the time to examine it. They are uniquIndentifier for CustoemrID and varchar for customerName"

Notice you are using int on the customerid variable. Change it to: Declare @CustomerID uniqueidentifier

Answer 6

Ok, Guys, Thanks for all ur efforts and replies.

Guess what? It works now. I dont know why? I think there was something wrong with my database permission setting. It is working now after i checked a permission from the table.

Thx. This site is great!