Can the usage of a Cursor dynamically generate a query statement?

Hi,
As far as my understanding goes, cursors can be used to fill in variables as
it iterates through a pre-defined hardcoded query.
Would like to know if I can make a dynamic query within a cursor and
subsequently
pump values obtained from this dynamic query into their respective variables
.
Appreciate any form of reply! Thanks!
An extract of the code (it doesn't run):
DECLARE Scursor CURSOR FOR
SELECT isnull(@.account_colname, '')
, isnull(@.trans_date_colname, '')
, isnull(@.trans_time_colname, '')
, isnull(@.trans_type_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.amount_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.seq_colname, '')
-- ,ROWIDTOCHAR(rowid) --ROWID
FROM isnull(@.source_tabname, '')
WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
AND isnull(@.contra_colname, '') IS NULL
OPEN ScursorHi Arthur,
The dynamic queries will be executed in a seperate memory space so u will
not be able to access any of the local variables. This holds good for your
cursors as well. u ll not be able to call them within your D-SQL.
rgds,
anu
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>|||Why cursors? Why dynamic SQL? Both are things that you should try to
avoid.
If you need more help, please specify your actual problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||The answer is yes, and the question is why do you want to do this?. Try
finding a set based solution to the problem and leave cursors as the last
resource in your pocket.
Example:
use northwind
go
declare @.my_cursor cursor
declare @.sql nvarchar(4000)
declare @.sd datetime
declare @.ed datetime
declare @.order_id int
declare @.order_date datetime
set @.sd = '19960101'
set @.ed = '19970101'
set @.sql = N'set @.c = cursor local fast_forward for select orderid,
orderdate from dbo.orders where orderdate >= '''
set @.sql = @.sql + convert(char(8), @.sd, 112) + N''' and orderdate < ''' +
convert(char(8), @.ed, 112) + N''''
set @.sql = @.sql + N'; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c = @.my_cursor output
if cursor_status('variable', '@.my_cursor') = 1
begin
while 1 = 1
begin
fetch next from @.my_cursor into @.order_id, @.order_date
if @.@.error <> 0 or @.@.fetch_status <> 0 break
select @.order_id, @.order_date
end
end
close @.my_cursor
deallocate @.my_cursor
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>

Can reports access session object and/or variables?

We are using SQL Reporting Services 2000.
We need the reports to be able to access a session variable or the request
object's server variables. How can we do this?Thank you for posting.
As for the SQL Server reporting service, the report components and controls
are well encapsulated to provide data centric processing. The underlying
ASP.NET application pipeline or components are hidden from the report
builder or developer, so we can not directly access session object in SQL
RS's report.
BTW, if you're programmtically request the SSRS report in ASP.NET web
application (through webservice), you can consider put the session state
manipulation code in ASP.NET application layer.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks for responding, Steven.
As an alternative to session, do you know if there is a way to do a global
variable for several reports to access? Our reports need to access a URL
that is different in the dev/test/production environments. One other person
suggested pulling this URL from a dataset, which is a decent workaround, but
it just seems like there should be a way to do a global variable in report
manager (we are using SRS2000).
"Steven Cheng[MSFT]" wrote:
> Thank you for posting.
> As for the SQL Server reporting service, the report components and controls
> are well encapsulated to provide data centric processing. The underlying
> ASP.NET application pipeline or components are hidden from the report
> builder or developer, so we can not directly access session object in SQL
> RS's report.
> BTW, if you're programmtically request the SSRS report in ASP.NET web
> application (through webservice), you can consider put the session state
> manipulation code in ASP.NET application layer.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>|||Thanks for your response,
So far I haven't found any other particular storage like the Session state
in normal web application. I also think the suggesion on using database to
store such data is reasonable. And you can consider defining a shared
datasource for such database table so that make the shared data accessing
more reusable among your report projects.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Can PL/SQL Acccept an inputted variable?

My "Query" refers to the ability to input variables in a PL/SQL Procedure.

Basically I am able to to input variables using the normal method
Exec procedure(variable1, variable2)

However I want to know if it is possible to code in such a way that the user inputs a variable when requested by the code:
For instance when the procedure is run, I want the DBMS to ask questions and accept the variables I put in to answer them
Sort of like the DBMS_OUTPUT but in reverse.
DMBS_INPUT perhaps??

Example:
SQL> Insert First Name?
SQL>

Thank you for any help with this...This is not possible...normally the database server running far away from the user and can't interact directly with him/her.
For such inputs you must implement a frontend...maybe an ms-access frontend can solve your problem.|||The project is an all Oracle affair, hopefully Oracle correct this problem because it would be handy to have this feature.
Thanks for your help and time Bart71|||If you are allowing your user to input directly through SQL Plus, I would suggest giving them a script to run rather than having them execute a package/procedure. Create a "test.sql" file as follows:

accept a prompt "Input First Name?"
execute my_procedure( '&&a' );

You can add whatever else you need; after that, from SQL Plus they would type:

SQL> @.test.sql

However, HIGHLY suggest having users go through a program interface rather than directly through SQL Plus...

JoeB