Ways around using a cursor?

[Snakeroot]

Can someone give me a couple of ideas about how to know if a cursor is necessary? I have a loop and I need to pull a variable line by line and immediately I'm thinking cursor, but after seeing all the warnings about not using cursors unless you really have to, I thought I'd ask for some input. Here's what I have:

SET @loop=0
WHILE @loop < @numberFranchises
 BEGIN
	 
  USE master
     
     SELECT @franchiseName = [Name] FROM master..sysdatabases
     WHERE OBJECT_ID([Name]+'..invoice') IS NOT NULL
     
	
  PRINT 'EXECUTE xp_cmdshell ''C:\batchfiles\' + @franchiseName + '-refresh.bat'
  PRINT @franchiseName + ' Complete'

  set @loop = @loop+1
 END

the select @franchiseName returns a list of franchises. I would like to use one franchise name at a time to plug into my command line. The code above, pulls the same first franchise name equivalent to the number of franchises.

I'm wondering if this is a good instance to use a cursor, or if there's a better way? Thanks!

SR

 

[mrdenny]

If you are going to be executing the above stored procedure then yes you need to use a cursor. If all you are doing is printing code which you'll then manually execute, you can do this via a single rowset.

SELECT 'EXECUTE xp_cmdshell ''C:\batchfiles\' + @[Name] + '-refresh.bat'
FROM master..sysdatabases
WHERE OBJECT_ID([Name]+'..invoice') IS NOT NULL

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Snakeroot]

Oops... I was just using print to make sure i got the command line right before I started running anything So, I guess a cursor it is then. Thanks for the feedback.

 

[ESquared]

There's a bigger question. Why are you running batch files from T-SQL? You can make it work, but should you make it work?

You can spread peanut butter with a drinking straw, but it's not necessarily the best way to get the job done. If someone asks on the forum "how to do X with a drinking straw" the simple answer Y may be technically right but practically wrong. In that case the correct answer is, "you don't, you use a knife!"

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Snakeroot]

We have a program that takes data from Quickbooks into SQL. It's designed to work on one QB datafile to be imported into one SQL database. The problem is we have multiple QB companies (some day up to 70).

I can run the program via a batch file and specify which company to port the data from, the problem is Quickbooks only allows the program to access one Quickbooks company at a time. So, right now I'm using windows scheduler to run the program multiple times through out the day for specific company files. What I would like to do is schedule company 1 to run, then when it completes, have company 2 run, then when it completes, have company 3 run, etc. I have looked for programs that are capable of this, but the ones I have found that can do this are too complex for this one need and subsequently cost a lot (in the thousands).

It so happens that SQL behaves in a linear fashion so by executing batch files, I get the "feature" of having one process complete before the next is started.

 

[philhege]

You can definitely do this with a set-based loop instead of a cursor. I assume the number of databases that meet your SELECT criterion matches the number of franchises to process.

USE master

SELECT [Name] as target, 0 as processed
INTO #temptable
FROM master..sysdatabases
WHERE OBJECT_ID([Name]+'..invoice') IS NOT NULL

WHILE (select count(*) from #temptable where processed = 0) > 0
BEGIN
SELECT TOP 1 @franchisename = target
FROM #temp
WHERE processed = 0

PRINT 'EXECUTE xp_cmdshell ''C:\batchfiles\' + @franchiseName + '-refresh.bat'
PRINT @franchiseName + ' Complete'

UPDATE #temptable SET processed = 1
WHERE target = @franchiseName
END

You could also use a table variable, but I'm getting too tired to write an example. Good night.

HTH,









Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[Snakeroot]

Very cool Phil! I'm following the logic. However, I'm getting this error when I run it. I'm not sure what's going on. Any ideas?

[highlight]
Server: Msg 1205, Level 13, State 1, Line 1
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[/highlight]

If I only run the top few lines of code, I get the same error message.

     SELECT [Name] as target, 0 as processed
INTO #temptable
FROM master..sysdatabases
     WHERE OBJECT_ID([Name]+'..invoice') IS NOT NULL

 

[philhege]

Try changing the name of the temporary table.

You don't have this inside any other code, do you? I just ran this on my instance without incident (although I did remove the OBJECT_ID function).

 

[Snakeroot]

No dice... I changed the names of both temp tables and it gives the same message

It works if I take out "INTO TABLE #..." (of course that defeats the purpose, but just in case that triggers anything for you...)

 

[Crowley16]

for small operations like this, it's better to use a cursor.

cursors are avoided due to performance problems when cursing through large datasets, if you don't have a large dataset, then don't worry about it and just use a cursor...

--------------------
Procrastinate Now!

 

[mrdenny]

Using the SELECT INTO command creates some strange locking, which would be why the deadlock happened.

Create the temp table first, then insert into it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com