Cursors
-
The question is about Using Cursors in SQL Server Stored Procedure.
I am trying to use a variable(@varStr ) in a cursor declaration. But I am unable to use it. Can we not use a variable in a cursor declaration.
Thanks
Imran
-
The cursor exists only for the @varStr execution lifetime. Can you post the whole code so we can figure out the best solution?
-
Thanks for the reply,
Here's a sample of the code:
DECLARE @WhereClause as varchar(4000)
SELECT @WhereClause = WhereCondition from Table1 WHERE EventID = 1
DECLARE curDetailRecords CURSOR FOR
(select FirstName From Customers where @WhereClause)
Imran
-
how many different filters could there be other than "WHERE EventID = 1" ?!?!
besides, remi wanted you to post the entire code so that he could give you a set-based solution and kill your cursor!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for the reply Sushila,
Here's what I am trying to do.
I have a stored Proc (which calls another 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, @WhereClause
FETCH FROM curEventRecords INTO @EventID, @WhereClause
END
CLOSE curEventRecords
DEALLOCATE curEventRecords
GO
---------------------------
In this proc i get the @EventID, @WhereClause and i want to pass it to the cursor declaration.
CREATE procedure myTestSp
@intEventID as int
AS
DECLARE @whseID as int
DECLARE @WhereClause as varchar(4000)
SELECT @WhereClause = WhereCondition from WEBJobEventHandler WHERE EventID = @intEventID
DECLARE curDetailRecords CURSOR FOR
(select r.WarehouseID From Receipts r Where @WhereClause )
OPEN curDetailRecords
FETCH NEXT FROM curDetailRecords INTO @whseID
WHILE @@FETCH_STATUS = 0
BEGIN
Print @ReceiptNumber
FETCH FROM curDetailRecords INTO @whseID
END
CLOSE curDetailRecords
DEALLOCATE curDetailRecords
--------------------------------------------
I am having problem at the @whereClause in red
Thanks a lot all for your help
-
Without looking at your code in depth, it is obvious that you want a where clause that can have any string in it so that you can filter based on different columns and values at different times.
Look on this website, or in SQL Server Books Online (the help manual that ships with SQL) for "dynamic sql", "sp_executesql" or "exec". These let you build an entire SQL query in a string and then execute it to get a resultset. You cannot execute something like
select *
from table
where @myWhereClause
SQL must have the columns explicitly spelled out - only the values (eg, column1 = VALUE) can be variables. If you are always filtering on a single column, then rework your code to be more like
select *
from table
where myColumn = @myValue
But I imagine that Remi will read your code and work out what you wanted to do with the cursor in the first place and probably help you remove it. I would like to, but I am late for work as it is
Hope it helps though!
Cheers,
Ian
-
Ian has a different take on this than mine...but again..remi will definitely stop by later on with the "right solution"...
imran - if i were to condense and combine your 2 stored procedures...here's what i get...is Ian right in believing that you want a dynamic filter ?! Do you just want to fetch a result set or is there some processing that you've left out from the code ?!
select W.EventID, R.WarehouseID, R.ReceiptNumber --(???) from WEBJobEventHandler W inner join Receipts R on W.EventID = R.EventID and W.Result = 0
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks a lot to all of you guys.
Yes Ian is right,
What is basicaly need is as follows
In the second proc that i listed, I am passing an "@eventID" as the parameter.
then I get the "@WhereClause" for the eventID. The @WhereClause will be different for different EventIDs.
Then I want to create a Cursor with the above query and want to insert values in another table.
So what i wanted basically was to create a differet query for the cursor every time.
DECLARE curDetailRecords CURSOR FOR
(select r.WarehouseID From Receipts r Where @WhereClause )
So if I follow what Ian says and build a the whole query into a variable how can i pass it to the cursor declaration. Can it be something like this:
DECLARE curDetailRecords CURSOR FOR (@SQLQuery )
Once again thanks a lot guys. I am little new to sql server
Imran
-
Yes Ian is right....well - Ian is obviously way better than I am at interpretations...
imran...while you're waiting for remi...here's something you can play around with...this code is for your second procedure - where if i understand correctly, you want only the eventID to be passed...really cannot test this myself, but it should be something like this...
DECLARE @EventID int DECLARE @EventType Nvarchar(500) DECLARE @SelectString Nvarchar(1000) SET @SelectString = N'select WarehouseID From Receipts Where EventID = @level' SET @EventID = ### SET @EventType = N'@level int' EXECUTE sp_executesql @SelectString, @EventType, @level = @EventIDthe really neat thing is, of course, the fact that you can "substitute parameter values for any parameters specified in the Transact-SQL string" so your EventID is taken care of here...
**ASCII stupid question, get a stupid ANSI !!!** -
Well I'm here now but I'm too tired to find and obvious fix (assuming there's one). I'll scratch my head on this one tomorrow.
While I'm gone, could you post the tables' ddl, some sample data and the expected output from the query so that I can something to work with?
-
remi - it's not the weekend yet..you can't just "go away" - not when so many live(lihood)s depend on you!!!
at any rate, if imran can comfirm this - here's what i understand he wants to do...
1) get all eventIDs from WEBJobEventHandler Where Result = ### (assumably a variable number)
2) pass these eventIDs one by one to the next procedure.
3) based on these eventIDs, get WarehouseID From Receipts table.
4) process resultset
if I have this right - then #3 is what i tried to provide a solution for...
**ASCII stupid question, get a stupid ANSI !!!** -
Just be forwarned - I despise cursors - and kill them any chance I get.
In any situation that that seems to require cursors - a table variable populated with the statement you would use for the cursor can usually be used. As I read what you have listed above, you could use this below.
If the EventID is a Primary Key in th WebJobEventHAndler talbe, then the declaration for @curEventRecord should be EventID INT Primary Key.
DECLARE @curEventRecords Table (EventID INT, WhereCondition VARCHAR(4000))
DECLARE @EventID INT, @WhereClause VARCHAR(4000)
INSERT INTO @curEventRecords
SELECT EventID , WhereCondition FROM WEBJobEventHandler WHERE Result = 0
WHILE EXISTS(SELECT TOP 1 1 FROM @curEventRecords)
BEGIN
SELECT @EventID = EventID, @WhereClause = WhereClause FROM @curEventRecords
--EXEC myTestSP @EventID, @WhereClause
EXEC('select r.WarehouseID From Receipts r Where ' + @WhereClause + ' AND EventID = ' + @EventID)
DELETE FROM @curEventRecords WHERE EventID = @EventID AND WhereClause = @WhereClause
END
Now if we only knew what you were going to do with the Warehouse ID - we probably could get rid of the dynamic build of the execute statement.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
Yet again Learn2Live - verry nice - didn't realize that the WhereCondition was actually a column till I read what you posted...talking of which, shouldn't the select be..
SELECT @EventID = EventID, @WhereClause = WhereCondition FROM @curEventRecords
I was under the impression that the Results = 0 was also dynamic ?!?!
ps:if anyone wants to go on a killing spree, this is the way to do it!
guess that's one way to Learn2Live ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Thank you again, sushila.
And you are correct, the Select Should be as you stated it. 
Now if only I could get rid of that dynamic SQL Execute, I'd really be happy...
FYI : I hate build strings just a lil less than cursors, and usually design around them - so to actually suggest that someone use one was a tad bit painful.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
Now if only I could get rid of that dynamic SQL Execute, I'd really be happy......Imran is the only one who can put you out of your misery!
ps: this seems to be a morning for destructive words & phrases..
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply