Cursor with Input parameters - 12-03-2004 , 12:49 AM
Is it possible to create a cursor with some input parameter on which the
cursor query can be based, if so then what is the syntax of using that?
Ejaz ul Haq
RE: Cursor with Input parameters - 12-03-2004 , 01:25 AM
Either create a stored procedure or a user-defined function that accepts the
Create Procedure dbo.MyProc
Declare myCursor Cursor Fast_Forward Read_Only
For Select Field2, Field3
Where Field1 = @Parameter1
.......... and so on
"Ejaz ul Haq" wrote:
RE: Cursor with Input parameters - 12-03-2004 , 01:41 AM
Thanx for that but i already have this solution,
actually i have migrated from PL/SQL of Oracle to SQL Server,
thats y i was searching for the same concept of that in here.
Any way thanx.
"Robert Davis" wrote:
Re: Cursor with Input parameters - 12-03-2004 , 02:00 AM
The best way to do it in Oracle may not be the best way in SQL Server.
Cursors in SQL Server are slow which is another good reason to avoid cursors
altogether where you can. If you give us some detail on your problem maybe
someone can suggest a set-based solution instead.
SQL Server MVP
Re: Cursor with Input parameters - 12-03-2004 , 03:45 AM
I have a TimeClass_tbl which contains the following fields.
[ID] [Name] [FromTime] [ToTime] [Date(just date, not time)]
I have to write a procedure with input of datetime datatype which can return
the ID of the specific entry matching that parameter. If the 'Date' column
can be null and on matching the date of supplied variable, it should return
the ID of that particular entry, but incase if there are more than one
records returned on matching this date, then i have to check that which entry
conforms to the time duration of 'FromTime' and 'ToTime', just for getting
those number of records and manipulating each record without employing any
extra burden of variables and one after another query i am taking cursors
If you have any better solution to this problem, i'll be thankfull to you.
Ejaz ul Haq
Re: Cursor with Input parameters - 12-03-2004 , 04:39 AM
The best way to post your table structure is as a CREATE TABLE statement,
including keys and constraints. For the moment I'll assume that your table
looks like this:
CREATE TABLE TimeClass (id INTEGER NOT NULL, name VARCHAR(20) NOT NULL,
fromtime DATETIME NOT NULL, totime DATETIME NOT NULL, date DATETIME NULL /*
??? PRIMARY KEY NOT SPECIFIED */)
In this case the query you want could be:
WHERE (@dt >= date AND @dt < DATEADD(DAY,1,date))
OR (@dt >= fromtime AND @dt < totime)
But possibly your problem is due to you storing dates and times separately
(I'm guessing that based on your column names). That's not a design I would
recommend but if you post the table structure and some sample data too then
maybe we can understand your requirements better.
This article explains what information to post to stand the best chance of
getting help with your problem:
Hope this helps.
SQL Server MVP