Cursor variable not declared issue

Ok, I missed the boat somewhere.
I prepared the query below to go though all the columns and tables in
the database and return the count of distinct values for each column and
label them with the table and column names
When I run the query, I receive the following error from the part
labeled #1:
Server: Msg 137, Level 15, State 2, Line 44
Must declare the variable '@.tbl_name'.
What I dont understand is why this error is occurring. I defined the
variable and populated it. I commented out part #1 and tried PRINT
@.tbl_name @.col_name which returned appropriate values.
I have a workaround of commenting out the part labeled #1 and instead
insert the following which produced a list of queries that I copied and
pasted into a new QA window and executed.
PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
'.' + @.col_name + '"' + ' from ' + @.tbl_name
I dont understand why I cannot substitute the variables in a query as I
wish to.
I am also considering building a command string and using exec
sp_executesql.
I welcome comments and suggestions on this matter.
-- -- --
DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
DECLARE CURS_sys_tables_and_cols CURSOR FOR
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
and sysobjects.name NOT like 'SYS%'
and sysobjects.name NOT IN ( LIST OF TABLES I DONT WANT)
order by sysobjects.name
OPEN CURS_sys_tables_and_cols
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- #1
PRINT @.tbl_name + '.' + @.col_name
Select count(distinct @.col_name) from @.tbl_name
PRINT '--'
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
END
CLOSE CURS_sys_tables_and_cols
DEALLOCATE CURS_sys_tables_and_cols
*** Sent via Developersdex http://www.examnotes.net ***SJM,
I think you'll need to use Dynamic SQL to use a variable for the table name
in your query i.e., EXEC or sp_executesql.
Check it out in the SQL BOL and at Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"SJM" <nospam@.devdex.com> wrote in message
news:eIAwXiRxFHA.624@.TK2MSFTNGP11.phx.gbl...
> Ok, I missed the boat somewhere.
> I prepared the query below to go though all the columns and tables in
> the database and return the count of distinct values for each column and
> label them with the table and column names
> When I run the query, I receive the following error from the part
> labeled #1:
> Server: Msg 137, Level 15, State 2, Line 44
> Must declare the variable '@.tbl_name'.
> What I don't understand is why this error is occurring. I defined the
> variable and populated it. I commented out part #1 and tried PRINT
> @.tbl_name @.col_name which returned appropriate values.
> I have a workaround of commenting out the part labeled #1 and instead
> insert the following which produced a list of queries that I copied and
> pasted into a new QA window and executed.
> PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
> '.' + @.col_name + '"' + ' from ' + @.tbl_name
> I don't understand why I cannot substitute the variables in a query as I
> wish to.
> I am also considering building a command string and using exec
> sp_executesql.
> I welcome comments and suggestions on this matter.
> -- -- --
> DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
> DECLARE CURS_sys_tables_and_cols CURSOR FOR
> select sysobjects.name, syscolumns.name from syscolumns, sysobjects
> where sysobjects.id = syscolumns.id
> and (sysobjects.xtype='U' or sysobjects.xtype='S')
> and sysobjects.name NOT like 'SYS%'
> and sysobjects.name NOT IN ( LIST OF TABLES I DON'T WANT)
> order by sysobjects.name
> OPEN CURS_sys_tables_and_cols
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> -- #1
> PRINT @.tbl_name + '.' + @.col_name
> Select count(distinct @.col_name) from @.tbl_name
> PRINT '--'
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> END
> CLOSE CURS_sys_tables_and_cols
> DEALLOCATE CURS_sys_tables_and_cols
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, I thought I might need to build strings and use sp_executesql.
Thanks for the pointer to the article, I missed it in my google
searches.
*** Sent via Developersdex http://www.examnotes.net ***

Cursor Help

Hi - Trying to work w/cursors but this is the error I receive:
'Cursorfetch: The number of variables declared in the INTO list must match
that of selected columns.' What am I a doing wrong. Below is my code.
Here's the code:
DECLARE @.Address_Line1 VARCHAR (100)
EXECUTE spGet_Statement_Rate '8', '2005'
--spGet_Statement_Rate looks like this:
--DECLARE RATE_CURSOR CURSOR GLOBAL FOR
--SELECT Address_Line1, Rate_ID FROM COMPANY
OPEN RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
WHILE (@.@.FETCH_STATUS = 0) BEGIN
print @.Address_Line1--FROM RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
END
--
CLOSE RATE_CURSOR
DEALLOCATE RATE_CURSORif u could post the ddl and sample data we could say whether u could do w/o
a cursor itself..
im not a fan of cursor based coding..
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong.
The message says it all. Your cursor select statement:
SELECT Address_Line1, Rate_ID FROM COMPANY
has 2 columns specified. However, your FETCH statements:
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
have only 1 column specified.
You can correct the issue either by omitting Rate_ID from the select list or
adding a variable for Rate_ID to your FETCH statements.
You might also revisit your code to see if you can accomplish the task
without a cursor. Set-based processing is often much more efficient than
cursor processing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||What do you mean by 'set based processing?' In a nutshell, I need to create
a result set of data. Then for each record returned, I need to execute a
bunch of stored procedures. I understand that cursors aren't the most
efficient, but this will run off hours and only once a month.
"Dan Guzman" wrote:

> The message says it all. Your cursor select statement:
> SELECT Address_Line1, Rate_ID FROM COMPANY
> has 2 columns specified. However, your FETCH statements:
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> have only 1 column specified.
> You can correct the issue either by omitting Rate_ID from the select list
or
> adding a variable for Rate_ID to your FETCH statements.
> You might also revisit your code to see if you can accomplish the task
> without a cursor. Set-based processing is often much more efficient than
> cursor processing.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
>
>|||> What do you mean by 'set based processing?'
Process the whole SET of rows at a time rather than one row at a time.
SQL Server is designed and optimized for set-based operations rather
than for cursor operations.
Aside from performance there are plenty of good reasons not to use
cursors. Set-based code is generally much more concise and easier to
develop, test, debug and maintain.
David Portas
SQL Server MVP
--|||> What do you mean by 'set based processing?' In a nutshell, I need to
> create
> a result set of data. Then for each record returned, I need to execute a
> bunch of stored procedures. I understand that cursors aren't the most
> efficient, but this will run off hours and only once a month.
You can't use set-based processing to execute procs for each row returned.
The point is that you can often use a set-based process instead of a looping
construct. Consider basic example:
DECLARE MyCursor CURSOR GLOBAL FOR
SELECT MyData FROM SomeTable
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyData
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM MyCursor INTO @.MyData
INSERT INTO MyTable(MyData)
VALUES(@.MyData)
END
CLOSE MyCursor
DEALLOCATE MyCursor
The above can also be accomplished with the statement below and perform much
better:
INSERT INTO MyTable (MyData)
SELECT MyData FROM SomeTable
It's best to use inline SQL statements when possible. If you need to keep
code in different procs, you can pass data using temp table instead of a
cursor.
Hope this helps.
Dan Guzman
SQL Server MVP|||Okay, here's the cursor i was going to use (this would be in a stored
procedure):
DECLARE RATE_CURSOR CURSOR GLOBAL FOR
SELECT
S.Statement_ID,
S.Rate_ID,
C.Company_Name,
C.Address_Line1,
C.Address_Line2,
C.City,
C.State,
C.Zip,
C.Phone,
C.creditAllow,
R.Rate_Type,
R.Amount_Per_Transaction,
ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
R.Percentage_Per_Transaction,
ISNULL (Percentage_Per_Transaction_RCK, 0) AS
Percentage_Per_Transaction_RCK,
ISNULL (Percentage_Per_Transaction_ARC, 0) AS
Percentage_Per_Transaction_ARC,
R.Monthly_Fee,
R.Monthly_minimum,
R.Rate_Per_Batch,
R.Discount_VPT,
R.Discount_Monthly_Fee,
R.Discount_RPB,
R.statement_fee,
R.service_fee,
R.ACHgateway_fee,
R.Reject_company_fee,
R.Reject_customer_fee,
R.ACHRefund_Fee,
R.ChargeBack_fee,
R.BadDE_fee,
R.NOC,
R.reserve_percentage,
R.GrossNet,
R.tranFeeCredit,
R.discountFeeCredit,
R.statementFeeCredit,
R.monthlyminCredit,
R.creditOutReturnFee,
RCK,
ARC,
C.RCKRebate,
C.CCDPPD_rebate_amount,
C.ARC_rebate_amount,
C.isRCK_rebate,
C.isARC_rebate,
C.isCCDPPD_rebate,
COMPANY_ID
FROM ST S, RT R, CO C
WHERE S.Rate_ID = R.Rate_ID and
S.Account_ID = C.Company_ID and
S.statement_Month = @.MONTH and
S.statement_Year = @.YEAR AND
C.ACCOUNT_STATUS = 1
Now, for earch record returned above, I will need to execute about 6 stored
procedures, passing in vaious parameters. I'm unfamilar as to how to loop
through a result set (as opposed to using a cursor) to achieve the same, so
if that's a better option, a code example would be appreciated.|||If you have to iterate through a result set for each row returned then
a cursor may well be the best way to do it. What you should generally
try to avoid is iteration in ANY form - other methods of doing so are
just a cursor in disguise. As you have a legacy proc designed to work
only with single rows you will have to decide whether it's worth a
re-write or if you can live with the cursor solution. The better
alternative in most cases is to avoid designing procs that impose any
single row limitations on your SQL code.
David Portas
SQL Server MVP
--|||To add on to David's response, I'd like to emphasize his point that the real
issue is your procs that operate on one record at a time. Ideally, you can
ditch both the cursor and the single-row procs and instead use set-based
processing. For example:
Instead of:
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM RATE_CURSOR INTO <variable list here>
EXEC MyInsertProc <variable list here>
END
Try:
INSERT INTO MyTable(<column list here> )
SELECT ...
I know that rewriting existing code can be painful and might not be worth
the effort in this case if your cursor performance is acceptable. However,
you should at least be familiar with set-based techniques so you can employ
them going forward as appropriate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D1E5E64E-3AA9-40C6-975D-C7A079AD20DF@.microsoft.com...
> Okay, here's the cursor i was going to use (this would be in a stored
> procedure):
> DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> SELECT
> S.Statement_ID,
> S.Rate_ID,
> C.Company_Name,
> C.Address_Line1,
> C.Address_Line2,
> C.City,
> C.State,
> C.Zip,
> C.Phone,
> C.creditAllow,
> R.Rate_Type,
> R.Amount_Per_Transaction,
> ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
> ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
> R.Percentage_Per_Transaction,
> ISNULL (Percentage_Per_Transaction_RCK, 0) AS
> Percentage_Per_Transaction_RCK,
> ISNULL (Percentage_Per_Transaction_ARC, 0) AS
> Percentage_Per_Transaction_ARC,
> R.Monthly_Fee,
> R.Monthly_minimum,
> R.Rate_Per_Batch,
> R.Discount_VPT,
> R.Discount_Monthly_Fee,
> R.Discount_RPB,
> R.statement_fee,
> R.service_fee,
> R.ACHgateway_fee,
> R.Reject_company_fee,
> R.Reject_customer_fee,
> R.ACHRefund_Fee,
> R.ChargeBack_fee,
> R.BadDE_fee,
> R.NOC,
> R.reserve_percentage,
> R.GrossNet,
> R.tranFeeCredit,
> R.discountFeeCredit,
> R.statementFeeCredit,
> R.monthlyminCredit,
> R.creditOutReturnFee,
> RCK,
> ARC,
> C.RCKRebate,
> C.CCDPPD_rebate_amount,
> C.ARC_rebate_amount,
> C.isRCK_rebate,
> C.isARC_rebate,
> C.isCCDPPD_rebate,
> COMPANY_ID
> FROM ST S, RT R, CO C
> WHERE S.Rate_ID = R.Rate_ID and
> S.Account_ID = C.Company_ID and
> S.statement_Month = @.MONTH and
> S.statement_Year = @.YEAR AND
> C.ACCOUNT_STATUS = 1
> Now, for earch record returned above, I will need to execute about 6
> stored
> procedures, passing in vaious parameters. I'm unfamilar as to how to loop
> through a result set (as opposed to using a cursor) to achieve the same,
> so
> if that's a better option, a code example would be appreciated.

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.
I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepape...ql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.
|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:

> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepape...ql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>
>
|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...[vbcol=seagreen]
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
rights.[vbcol=seagreen]
opened,[vbcol=seagreen]
using[vbcol=seagreen]
has[vbcol=seagreen]

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepap...Sql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:

> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepap...Sql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>
>|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...[vbcol=seagreen]
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
>
rights.[vbcol=seagreen]
opened,[vbcol=seagreen]
using[vbcol=seagreen]
has[vbcol=seagreen]

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:
> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
> >I wanted to know the internal workings of a 'cursor'.
> > What happens inside SQL Server from the time that it is declared, opened,
> > when it is used and finally closed and deallocated?
> > Have searched the web - but apart from examples and the pros/cons of using
> > them - have not come across the information that I want.
> > Can someone please provide this info to me or direct me to a site that has
> > this info?
> > Cheers!
> > SQLCatz.
>
>|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
> > I assume you are talking about Server side cursors?
> >
> > This article might help you:
> >
> > http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
> >
> >
> > --
> > Wei Xiao [MSFT]
> > SQL Server Storage Engine Development
> > http://blogs.msdn.com/weix
> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> > news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
> > >I wanted to know the internal workings of a 'cursor'.
> > > What happens inside SQL Server from the time that it is declared,
opened,
> > > when it is used and finally closed and deallocated?
> > > Have searched the web - but apart from examples and the pros/cons of
using
> > > them - have not come across the information that I want.
> > > Can someone please provide this info to me or direct me to a site that
has
> > > this info?
> > > Cheers!
> > > SQLCatz.
> >
> >
> >