help needed -problem with looping and dynamic sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help needed -problem with looping and dynamic sql

Author

Topic

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-08-24 : 02:27:59

Hi,
i am stuck with a problem, i tried number of solutions but i could find anything that could work for me.

basically i need to loop through a number of records and write an entry in my log table using another sp.
the problem is the table name is also dynamically generated by passing account id as variable
ex: strAccountID ='27967'

so finally i can get the table name as 'tblRoutes' + '27967'

i have created DML's for some sample tables and also a query (instead of an SP which basically explains what i need )

CREATE TABLE tblRoutes27967
(
ID INT PRIMARY IDENTITY,
ProgramID VARCHAR(100),
RouteID VARCHAR(100)
)

insert into tblRoutes27967(ProgramID, RouteID)
VALUES ( '27967552369', '120')

CREATE TABLE [dbo].[tblRouteOptions27967](
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[ProgramID] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RouteID] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RouteOptionID] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RouteOptionName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[dtStamp] [datetime] NOT NULL CONSTRAINT [DF_tblRouteOptions27967_dtStamp] DEFAULT (getdate()),
CONSTRAINT [PK_tblRouteOptions27967] PRIMARY KEY CLUSTERED
(
[ProgramID] ASC,
[RouteID] ASC,
[RouteOptionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tblRouteOptions27967(ProgramID, RouteID, RouteOptionID, RouteOptionName)
VALUES ( '27967552369', '120', '123456', 'name1')
INSERT INTO tblRouteOptions27967(ProgramID, RouteID, RouteOptionID, RouteOptionName)
VALUES ( '27967552369', '120', '1234567', 'name2')
INSERT INTO tblRouteOptions27967(ProgramID, RouteID, RouteOptionID, RouteOptionName)
VALUES ( '27967552369', '120', '1234567', 'name3')
INSERT INTO tblRouteOptions27967(ProgramID, RouteID, RouteOptionID, RouteOptionName)
VALUES ( '27967552369', '220', '12345', 'name1')

/*** all these are part of the sp
but for convenience i made it as a Query
***/

DECLARE @strProgramID char(11)
SET @strProgramID = '27967552369'
DECLARE @strRouteID char(3)
SET @strRouteID = '120'
DECLARE @strRouteName varchar(50)
SET @strRouteName = 'Route3'
DECLARE @intApiUserID numeric
SET @intApiUserID = 15
declare @intUserID numeric
SET @intUserID = 15
declare @strAccountID char(5), @strDescription varchar(100)
set @strAccountID = left(@strProgramID, 5)
declare @sql nvarchar(4000)
declare @sql1 nvarchar(4000)
declare @strRouteOptionID char(3)
declare @strRouteOptionName varchar(50)

/************
1. need to get the records from a table and delete all of them
*****************/

set @sql = 'Delete from tblRoutes' + @strAccountID + ' where ProgramID= ''' + @strProgramID + ''' and RouteID = ''' + @strRouteID + ''''
exec (@sql)

/**
delete from another table and for each deletion i need to save a description in another table
which is taken care by anothe SP [spAddRouteOptionHistory]
*/

here is the problem i am facing as there could be multiple records in this table i need to use a loop to deletion and for each deletion i need to save a description, in another table using sp

EXEC [spAddRouteOptionHistory] @strProgramID, @strRouteID, @strRouteOptionID, @strRouteOptionName, @intApiUserID, @strDescription, -1, 'tmp' -- this will work fine

so i need something like a loop or cursor... with which i need to loop through

i want to do something like this

set @sql = 'select routeoptionid,routeoptionname from tblRouteOptions' + @strAccountID + ' where ProgramID = ''' + @strProgramID + ''' and RouteID = ''' + @strRouteID + ''''

DECLARE routeoptiondeletecursor CURSOR for
exec (@sql)

open routeoptiondeletecursor
fetch next from routeoptiondeletecursor into
@strRouteOptionID ,@strRouteOptionName

while @@FETCH_STATUS = 0
BEGIN
set @sql1 = 'Delete from tblRouteOptions' + @strAccountID + ' where ProgramID= ''' + @strProgramID + ''' and RouteID = ''' + @strRouteID + ''' and RouteOptionID = ''' + @strRouteOptionID + ''''
exec (@sql1)

/*Code for entering data into tblRouteOptionsHistory*/
SET @strDescription = dbo.fnGetUserName(@intApiUserID) + ' - Route Destination Deleted while Route is deleted'
EXEC [spAddRouteOptionHistory] @strProgramID, @strRouteID, @strRouteOptionID, @strRouteOptionName, @intApiUserID, @strDescription, -1, 'tmp'

fetch next from routeoptiondeletecursor into
@strRouteOptionID ,@strRouteOptionName
END

but this is not possible as i guess we cannot use exec in the cursor...

i want to try with table variables or with temporary tables but the problem is
for inserting records into the table variable or temporary variable ... dynamic sql gives me an error as the name is not declared.

please help me out with this one as i ran out of options.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-24 : 07:54:09

quote:
basically i need to loop through a number of records and write an entry in my log table using another sp.
the problem is the table name is also dynamically generated by passing account id as variable
ex: strAccountID ='27967'

Step 1: DO NOT CREATE TABLES ON THE FLY. If you somehow have the need to dynamically create tables, your design has SERIOUS flaws. Are you REALLY telling me EACH client has its own table????
tblRoutes is the ONLY route table you need. Put a key in that tells you which client the route belongs to.
Same for RouteOptions. Then you wont have to deal with dynamic sql at all.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-08-24 : 09:25:51

hi,
thank you for the reply.
firstly the tables are created as soon as a client is added into the system.

so each and every client has its own table. i don't know why the system was designed using this approach. i guess it is because of the amount of data each client would have at some point of time.
that's the only reason i can think of. i wont get the permission to alter way the system works at the moment.

but for the time being i need to implement some new features, for this one i need the above criteria to work.

i could do this at the front end by getting the routeoptionsID's as a dataset and loop through in the front end, so that only one record is deleted at any time in the second table ( using the unique combination of routeID, routeoptionID, and programID ).

but in this way i have to hit the DB like 8 to 10 times from the client for a particular operation. which is not desirable, so thats why i was looking for some sql solution which would be more efficient.

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-08-25 : 11:04:00

hi,
i guess i found a way to do it.

i will get the count of records which matches the criteria in the second table.
i will loop through the records and delete top 1 from the selected list in the table until the count is zero. this will work for me for the time being.

thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources