getting 100 rows with values from 1 - 100

I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
KarlCheck out:
http://msdn.microsoft.com/library/d...r />
p03k1.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<klumsy@.xtra.co.nz> wrote in message
news:1115860445.064283.61420@.f14g2000cwb.googlegroups.com...
I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
Karl

Get Value from CURSOR

I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far...

My question is, how do I get the value out of the Cursor? There's only one field.

Declare @.Day as int
Declare @.Plant as varchar(30)

SET NOCOUNT ON

CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)

Declare Facility_Cursor CURSOR
For Select Distinct(Facility) From ProductionHistory
OPEN Facility_CURSOR
Declare @.Facility_Cursor as sysname

FETCH NEXT From Facility_CURSOR into @.Facility_Cursor

WHILE @.@.FETCHSTATUS = 1

--YESTERDAY
Set @.Day = -2

Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'

--TODAY
SET @.Day = -1
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'

FETCH NEXT FROM Facility_CURSOR into @.Facility_Cursor

CLOSE Facility_Cursor
DEALLOCATE Facility_CURSOR

SET NOCOUNT OFF

Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESCI see that you are trying to pass a variable "@.Facility" to your sproc without defining it, and the results of the cursor are being placed into @.Facility_Cursor. If you change the variable declaration to @.Facility and then FETCH NEXT From Facility_CURSOR into @.Facility it just might work!|||That did the trick. I knew it was something simple. Thanks!

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbname sysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regardsHave you looked at sp_helpdb? Does it give you what you want?
--
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards

Get records count from SQL cursor

Hi experts,
I have created a SQL cursor for records processing in a stored procedure. I
probably can use the @.@.Cursor_Rows function in order to obtain total rows of
record contained inside the cursor. But once I declare the cursor as
FAST_FORWARD, it always return me -1. I need to declare the cursor as
FAST_FORWARD as it really helps in tuning the performance. Else my stored
procedure will take longer time to execute.
Any other way I can use to get the total records being returned? I've tried
to signal another SQL statement to perform the records count but this seems
to create redundant overhead. I believe if I can do anything to existing
cursor without having extra Select Count statement, it would help to reduce
unnecessary processing and shorten the overall time required.
Really appreciate for any advice or suggestion. Thanks a lot.Can you test perfoermance between your current cursor declaration and the on
e
I am posting?
declare my_cursor cursor
local
forward_only
static
read_only
for
...
AMB
"LBT" wrote:

> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've trie
d
> to signal another SQL statement to perform the records count but this seem
s
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to reduc
e
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||If performance is a concern for you then how about getting rid of the
cursor altogether? Cursors are rarely a good idea.
David Portas
SQL Server MVP
--|||Thanks for the suggestion. The time taken is still approximately equal to th
e
cursor which is declared without those keywords. And I can't declare the
cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I forgo
t
to write out this concern in my previous post).
"Alejandro Mesa" wrote:
> Can you test perfoermance between your current cursor declaration and the
one
> I am posting?
> declare my_cursor cursor
> local
> forward_only
> static
> read_only
> for
> ...
>
> AMB
>
> "LBT" wrote:
>|||You can use select count(*)... yada,,, the optimizer can use the index
entries instead of having to read the data ( if there is a supporting
index.)
You can also use select @.@.rowcount AFTER the query...
You have to open the cursor before the rowcount info is available for
cursors...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
> I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
> of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've
> tried
> to signal another SQL statement to perform the records count but this
> seems
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to
> reduce
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||I performed the following test but @.@.rowcount return me 0. Please check for
me if there is something wrong with the code. Thanks
---
declare @.temp varchar(50)
declare my_cursor cursor fast_forward for
select column01 from table01
open my_cursor
select @.@.rowcount
fetch my_cursor into @.temp
while @.@.fetch_status = 0
begin
print @.temp
fetch my_cursor into @.temp
end
close my_cursor
deallocate my_cursor
----
--
"Wayne Snyder" wrote:

> You can use select count(*)... yada,,, the optimizer can use the index
> entries instead of having to read the data ( if there is a supporting
> index.)
> You can also use select @.@.rowcount AFTER the query...
> You have to open the cursor before the rowcount info is available for
> cursors...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
>
>|||I do agree that cursor is definately not a good idea to be used...I did thin
k
of using table variable previously but since I need to use dynamic SQL and
table variable is not supported to be used together with dynamic SQL...any
other way to get rid of cursor? Will temp table use the equivalent overhead
as cursor? I think i need to temporarily store records (based on passed-in
filtering criteria) to somewhere else so that i'm able to loop the records
and perform further analysis. My stored procedure will return one summarized
result based on passed-in filtering criteria.
"David Portas" wrote:

> If performance is a concern for you then how about getting rid of the
> cursor altogether? Cursors are rarely a good idea.
> --
> David Portas
> SQL Server MVP
> --
>|||> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
Who said that?
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.c cursor
set @.sql = N'set @.c = cursor local forward_only static read_only for select
orderid, orderdate from orders where orderdate >= ''19960101'' and orderdate
< ''19970101''; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c output
if cursor_status('variable', '@.c') >= 0
begin
print @.@.cursor_rows
close @.c
deallocate @.c
end
go
I am not advocating for cursors.
AMB
"LBT" wrote:
> Thanks for the suggestion. The time taken is still approximately equal to
the
> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
>
> "Alejandro Mesa" wrote:
>|||Your reply just poses more questions upon questions. Why dynamic SQL?
Dynamic SQL, like cursors, is something you should aim to avoid, or at
least minimize. Why "loop the records"? Or for that matter, why
"temporarily store records"? (BTW the term "rows" is generally
preferred to "records" in RDBMS).
In short, the best way to get help is to describe *what* you want to do
rather than *how* you think you should go about it. Tell us what your
goal is and show us your data structure as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> My stored procedure will return one summarized result based on
passed-in filtering criteria. <<
Get a **basic** software engineering book and look up coupling and
cohesion. In a properly designed module, you do not pass in criteria.
That module would have no cohesion. This is far more basic than SQL;
this is how to be any kind of programmer.
Let's go back to square one and find out what you are trying to do and
then we can look for a set-based, relational approach that will give
you a maintainable procedure. You might also want to consider taking
college courses on software engineering, then learn databasess and data
modeling.

Get records count from SQL cursor

Have you thought of declaring a int then adding one to it
per record ?
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan

>--Original Message--
>Hi experts,
>I have created a SQL cursor for records processing in a
stored procedure. I
>probably can use the @.@.Cursor_Rows function in order to
obtain total rows of
>record contained inside the cursor. But once I declare
the cursor as
>FAST_FORWARD, it always return me -1. I need to declare
the cursor as
>FAST_FORWARD as it really helps in tuning the
performance. Else my stored
>procedure will take longer time to execute.
>Any other way I can use to get the total records being
returned? I've tried
>to signal another SQL statement to perform the records
count but this seems
>to create redundant overhead. I believe if I can do
anything to existing
>cursor without having extra Select Count statement, it
would help to reduce
>unnecessary processing and shorten the overall time
required.
>Really appreciate for any advice or suggestion. Thanks a
lot.
>.
>I've tested to append 1 to an int variable each time while looping the curso
r
but the effect is not so significant to boost the performance...Anyway,
thanks for the suggestion...
"Peter The Spate" wrote:

> Have you thought of declaring a int then adding one to it
> per record ?
> Peter
> "Status quo, you know, that is Latin for "the mess we're
> in."
> Ronald Reagan
>
> stored procedure. I
> obtain total rows of
> the cursor as
> the cursor as
> performance. Else my stored
> returned? I've tried
> count but this seems
> anything to existing
> would help to reduce
> required.
> lot.
>