Cursors
-
Well I can hide the cursor and the while from this but I'm not sure that the performance would be any better. I however can't get out of the concatenation
.I'm still waiting for the sample data/results and ddl to test my theories though.
-
I'm still waiting for the sample data/results and ddl to test my theories though.......hope you're not holding your breath tho' - your help/presence is too invaluable...
**ASCII stupid question, get a stupid ANSI !!!** -
Got other threads to answer and some reports to build... got my days of worked planned already so I can live without this problem
. -
thanks a lot to all of you for your help. For a newbie like me, I was able to learn a lot by just reading your comments and REALLY understanding what i was trying to do. Finally was able to solve it. I got rid of the Cursor in the second Stored proc. I am getting the @eventID and @WhereClause from the first SP and then executing it and inserting some values in another table.
THank again to all of u for having the patience and helping me
Love U all
Imran
-
Can we see the final code??
-
imran - you should know that it's never easy getting away from remi...the only way to keep him off your back is by posting your final code!
besides...he/someone else may be able to improve it still further (if there's room for improvement)..
**ASCII stupid question, get a stupid ANSI !!!** -
Still waiting to test my theory
. -
have a feeling it's going to be a loooooooong wait!
**ASCII stupid question, get a stupid ANSI !!!** -
We'll see...
-
Sorry guys for the late response.
But here's what I did for the second SP. In this SP, the EventID is passed and I get the @Whereclaause. I then execute the query and insert the records in another table.
Thanks Again to all.
CREATE procedure myTestSp
@intEventID as int
AS
DECLARE @intwhseID as varchar(6)
DECLARE @intReceiptNumber as varchar(7)
DECLARE @intCropYear as varchar(4)
DECLARE @WhereClause as varchar(4000)
SELECT @WhereClause = WhereCondition from WEBJobEventHandler WHERE EventID = @intEventID
SET @sqlInsert = 'INSERT INTO WEBJobBatchDetail (EventID, BatchDetail) SELECT '
SET @sqlInsert = @sqlInsert + CONVERT(varchar,@intEventID)
SET @sqlInsert = @sqlInsert + ', CAST(ReceiptNumber AS varchar(7)) '
SET @sqlInsert = @sqlInsert + ' From Receipts r inner join Holders h1 on r.HolderID = h1.holderID '
SET @sqlInsert = @sqlInsert + @WhereClause
exec(@sqlInsert)
GO
-
And the whole process looks like what???
-
I created a Job in sql server, Which executes a SP (usp_WEBJobStep1)every 1 hour. This SP retreives eventIDs from the table for Result = 0.
For each EVentID I get the @whereClause , execute the dynamic sql and then insert the rows fetched in another table.
the first SP:
Create Procedure usp_WEBJobStep1
AS
DECLARE @EventID as int
DECLARE @WhereClause as varchar(5000)
DECLARE curEventRecords CURSOR FOR (select EventID, WhereCondition From WEBJobEventHandler Where Result= 0)
OPEN curEventRecords
FETCH NEXT FROM curEventRecords INTO @EventID, @WhereClause
WHILE @@FETCH_STATUS = 0
BEGIN
exec myTestSp @EventID
--update the WEBJobEventHandler set Result = 1 for this EventID
UPDATE WEBJobEventHandler SET Result= 1 WHERE EventID = @EventID
FETCH FROM curEventRecords INTO @EventID, @WhereClause
END
CLOSE curEventRecords
DEALLOCATE curEventRecords
GO
The Second SP:
CREATE procedure myTestSp
@intEventID as int
AS
DECLARE @intwhseID as varchar(6)
DECLARE @intReceiptNumber as varchar(7)
DECLARE @intCropYear as varchar(4)
DECLARE @WhereClause as varchar(4000)
SELECT @WhereClause = WhereCondition from WEBJobEventHandler WHERE EventID = @intEventID
SET @sqlInsert = 'INSERT INTO WEBJobBatchDetail (EventID, BatchDetail) SELECT '
SET @sqlInsert = @sqlInsert + CONVERT(varchar,@intEventID)
SET @sqlInsert = @sqlInsert + ', CAST(ReceiptNumber AS varchar(7)) '
SET @sqlInsert = @sqlInsert + ' From Receipts r inner join Holders h1 on r.HolderID = h1.holderID '
SET @sqlInsert = @sqlInsert + @WhereClause
exec(@sqlInsert)
Go
-
Since you don't understand the words
SAMPLE
DATA
RESULTS
DDL
here's a totally working solution that you won't be able to use directly into your system without rewriting the select query :
This is the query that does the work :
EXEC master.dbo.xp_execresultset 'Select ''Insert into dbo.Target (nbr, WhereCond) Select nbr, '''''' + WhereCond + '''''' from dbo.Data '' + WhereCond As execQuery from dbo.Transfers', 'NameOfTheDBThisMustBeExecutedFrom'
And here's a full test script :
IF object_id('Data') > 0
DROP TABLE dbo.Data
GO
IF object_id('Target') > 0
DROP TABLE dbo.Target
GO
IF object_id('Transfers') > 0
DROP TABLE dbo.Transfers
GO
CREATE TABLE dbo.Data (nbr int not null primary key clustered)
GO
Insert into dbo.Data (nbr) values (24)
Insert into dbo.Data (nbr) values (64)
Insert into dbo.Data (nbr) values (35)
Insert into dbo.Data (nbr) values (7536)
Insert into dbo.Data (nbr) values (3)
Insert into dbo.Data (nbr) values (1)
Insert into dbo.Data (nbr) values (38)
Insert into dbo.Data (nbr) values (-14)
Insert into dbo.Data (nbr) values (-4)
Insert into dbo.Data (nbr) values (0)
GO
CREATE TABLE dbo.Target (nbr int not null primary key clustered, WhereCond varchar(100) not null)
GO
CREATE TABLE dbo.Transfers (WhereCond varchar(100) not null primary key clustered)
GO
Insert into dbo.Transfers (WhereCond) values (' WHERE nbr 0 and nbr 64')
GO
Select * from dbo.Data
Select * from dbo.Transfers
Select * from dbo.Target
GO
EXEC master.dbo.xp_execresultset 'Select ''Insert into dbo.Target (nbr, WhereCond) Select nbr, '''''' + WhereCond + '''''' from dbo.Data '' + WhereCond As execQuery from dbo.Transfers', 'test'
GO
Select * from dbo.Target --everything except 64
GO
DROP TABLE dbo.Data
DROP TABLE dbo.Target
DROP TABLE dbo.Transfers
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply