Create a variable cursor

Magno

Hi,

I want to create a cursor dynamically to a non determined company\table in NAV.
like this:

DECLARE @Company varchar( 50);
DECLARE @Table varchar( 50);
DECLARE @EndPoint int;
SET @Company = 'Company';
SET @Table = 'TableName';
DECLARE curData CURSOR LOCAL FAST_FORWARD FOR
	Select DISTINCT EndPoint
	from [@Company$@TableName]
OPEN curData

FETCH NEXT FROM curData INTO @EndPoint

WHILE @@FETCH_STATUS = 0
    BEGIN
		print @EndPoint
		-- GET NEXT
		FETCH NEXT FROM curData INTO @EndPoint
	END
CLOSE curData
DEALLOCATE curData

Is this possible to do?

Belias

not sure to understand you (i don't know t-sql a lot), but maybe you can search the help and then mibuso for "recordRef" (and fieldref) variable type

Magno

that is not really an option (recordRef and fieldRef), i know these, because the SQL Statements i'm making are as a replacement for the field and recordRef. The Ref's take too long for what we want to do.

I have the prototype code in SQL, but specific for 1 company now and need to built it into a stored procedure.

garak

So and whats there your question and why do you need it direktcly from NAV
Per default NAV can't run direkt TSQL. Only via ADO or if you use a View and in this view is TSQL (for example the view call a function)

Magno

Who said anything about running this from navision.

I run this in SSMS (SQL Server Management Studio) just because i can use the speed of SQL Server in my process then. My goal is to create a stored procedure to call in Navision using ADO.

garak

Magno wrote:

Who said anything about running this from navision.

Because you post it in the NAV/Navision subforum.

My goal is to create a stored procedure to call in Navision using ADO.

OK, also no problem. For ADO here are many examples, and how to create and save a sp under the Management Studio also simple. How to run a sp from NAv with using ADO is also no prob and you found it also if you search the forum.
Is your final question how to display the resultset of the sp in NAV, for example in a form?

Magno

no, my question was if there is another way to create a dynamic cursor, i was wondering if other people have done this in NAV.

The sp and ADO part i have done before.

garak

One of the changes in NAV 5, was to change from primarily making use of Fast-Forward cursor types to Dynamic cursors. The same change was implemented in version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920.

Before 5 / 4.03 Update 6 NAv use ever the FF courser type.

http://blogs.msdn.com/microsoft_dynamic ... types.aspx

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

stryk

I guess you are looking for something like this:

set statistics io off

declare @cust_name varchar(30)
declare @tab_name varchar(128)
set @tab_name = 'CRONUS 403$Customer'

exec ('declare dyn_cur cursor for select [Name] from [' + @tab_name + ']')
open dyn_cur
fetch next from dyn_cur into @cust_name
while @@fetch_status = 0 begin
  print @cust_name
  fetch next from dyn_cur into @cust_name
end
close dyn_cur
deallocate dyn_cur

Hence, the "EXEC" of the dynamic SQL does the trick ...

Magno

stryk wrote:

I guess you are looking for something like this:

set statistics io off

declare @cust_name varchar(30)
declare @tab_name varchar(128)
set @tab_name = 'CRONUS 403$Customer'

exec ('declare dyn_cur cursor for select [Name] from [' + @tab_name + ']')
open dyn_cur
fetch next from dyn_cur into @cust_name
while @@fetch_status = 0 begin
  print @cust_name
  fetch next from dyn_cur into @cust_name
end
close dyn_cur
deallocate dyn_cur

Hence, the "EXEC" of the dynamic SQL does the trick ...

Great, this is indeed what i wanted. I looked into the exec, but didn't know the cursor would be known outside the exec as well.
Thx!