Can I define global or static variables?

Hi, I need to define a global or static variable, but I don't know how I can do it.
I need to number each "Detail" line, but I can't use the "Record Number" of "Special Fields" because each database record can create more than one detail line. I try to solve this problem using a global or static variable, but if isn't possible, what do you suggest me?

Thanks and sorry for my bad English.I've just done this very thing in a report I've been working on. The method I used is dependent on using a group header along with the details section.

1) Create a formula field (I called mine CounterReset) and use the following formula:
WhilePrintingRecords;
Global numbervar GroupRecCount;
GroupRecCount := 0;

2) Add this field to your group header. You can supress it if you don't want it to show on your report.

3) Crate another formula field (I called mine CountOfRecordsInGroup) and paste the following formula:
WhilePrintingRecords;
Global numbervar GroupRecCount;
GroupRecCount := GroupRecCount + 1;

4) Use this field in your details section as the line number. It will reset to zero when your groupnumber advances and *sounds* like what you are looking for from the description of your problem.

Hope this helps!

Z|||Thank you very much zilonox!! I've been asking this for several days and nobody could help me.
Greetings,
Christian J.

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this
be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in m
y case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the V
ARYING
keyword. From the stored procedure documentation (which I kind of assumed t
hat
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was nece
ssary when
using a cursor variable. But, it appears not to be the case, and that was t
he one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing dat
a
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the curso
r variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of th
e dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it compla
ins that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the o
riginal "By
Name" and the variable) -- and I need to essentially close both handles befo
re the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in
my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the
VARYING
> keyword. From the stored procedure documentation (which I kind of assumed
that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was ne
cessary when
> using a cursor variable. But, it appears not to be the case, and that was
the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor
output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could b
e found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cur
sor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dyna
mic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it comp
lains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the
original "By
> Name" and the variable) -- and I need to essentially close both handles before the

cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
I'm
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
--
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the VARYING
keyword. From the stored procedure documentation (which I kind of assumed that
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
using a cursor variable. But, it appears not to be the case, and that was the one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing data
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the cursor variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of the dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it complains that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the original "By
Name" and the variable) -- and I need to essentially close both handles before the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the VARYING
> keyword. From the stored procedure documentation (which I kind of assumed that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> using a cursor variable. But, it appears not to be the case, and that was the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > Cursors are usually best avoided because of their performance/resource
> > implications. Erland has an article on alternative methods for sharing data
> > between SPs:
> >
> > http://www.sommarskog.se/share_data.html
> >
> > For completeness, here's an amended version of your code:
> > ...
> > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > ...
> >
> > Now reference the cursor by variable (@.cursor).
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could be found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cursor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dynamic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it complains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the original "By
> Name" and the variable) -- and I need to essentially close both handles before the
cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> > David,
> >
> > You're right -- I appreciate that cursors aren't wholly performant, but in my case,
I'm
> > writing a management procedure that lends itself well to using cursors.
> >
> > Thanks for the link on other techniques for sharing data. :-)
> >
> > I think you solved my issue! From what I can tell, you merely removed the VARYING
> > keyword. From the stored procedure documentation (which I kind of assumed that
> > [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> > using a cursor variable. But, it appears not to be the case, and that was the one
> > combination I *didn't* try!
> >
> > Thanks so much! :-)
> >
> > John Peterson
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > > Cursors are usually best avoided because of their performance/resource
> > > implications. Erland has an article on alternative methods for sharing data
> > > between SPs:
> > >
> > > http://www.sommarskog.se/share_data.html
> > >
> > > For completeness, here's an amended version of your code:
> > > ...
> > > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > > ...
> > >
> > > Now reference the cursor by variable (@.cursor).
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> >
> >
>