Issues with cursor

Question

Issues with cursor

Susan-322874

SSCrazy

Points: 2300
More actions
August 5, 2006 at 7:21 pm

#115948

What I'm trying to do is go through a list of counties and create a table for each county.
I have a list of counties and am using the standard of countyname
(i.e. CountySantaClara)
What am I doing wrong?

set
nocount on

declare
@tablename nvarchar(100)
declare
table_cursor CURSOR for
select
distinct (county) from ziptrendindex
open
table_cursor

fetch
next from table_cursor
into
@tablename
while
@@fetch_status = 0
begin
IF
NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[@tablename]') AND type in (N'U'))
BEGIN
CREATE
TABLE [dbo].[county+@tablename](
[SA_PROPERTY_ID] [int]
NOT NULL,
[ReplyAvgWeight] [int]
NULL
)

end
close
table_cursor
deallocate
table_cursor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lance Sanders SSC Eights! Points: 903 More actions · Answer 1

The biggest problem is that most DDL statements don't like variables, and require explicit specifications. You'll have to build & execute the create table statement dynamically. The other problem is that you're missing an END and another FETCH.

declare @sql varchar(8000)
while @@fetch_status = 0
begin
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[@tablename]') AND type in (N'U'))
  BEGIN
    set @sql = 'CREATE TABLE [dbo].[county'+@tablename+']([SA_PROPERTY_ID] [int] NOT NULL, [ReplyAvgWeight] [int] NULL)'
    exec (@sql)
  end
  fetch next from table_cursor into @tablename
end

Sergiy SSC Guru Points: 110208 More actions · Answer 2

> What am I doing wrong?

Creating tables for each country.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

SG,

Man, Serqiy is right and Celko would have fun this this one... Why are you splitting an attribute into separate tables? What is it that you are trying to do that makes you think you need to do such a non-RDBMS thing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)