Convert Stored Procedure Into Access 2003

[anilvaghela]

Hello.....I have a stored procedure which does magic within MS SQL Express 2005.

Off course I am only using the ms sql express software on only this one table.

I want to be able to convert the following stored procedure into access.....


----------------------------------------------------------------------
Create PROC dbo.ShowHierarchy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @u_id int, @namesite varchar(50), @unique_id varchar(50)


set @unique_id = (Select unique_id from dbo.tbl_search Where u_id=@Root)
set @namesite = (Select namesite from dbo.tbl_search Where u_id =@Root)

PRINT REPLICATE ('', @@NESTLEVEL *5) +@unique_id +',' +@namesite

Set @u_id= (Select MIN(u_id) From dbo.tbl_search where parent_id =@Root)

While @u_id is not null
BEGIN
Exec dbo.ShowHierarchy @u_id
set @u_id =(Select MIN(u_id) FROM dbo.tbl_search WHERE parent_id = @Root and u_id > @u_id)
END
End
GO
--------------------------------------------------------------------------

Your help would be appreciative!

Thanks

Anil

http://www.heavyvibesonline.co.uk

 

[SQL_Hell]

Why does it need to be converted to access? cant you just call the procedure from access?

Surely you are not downscaling to Access from SQL server?

 

[anilvaghela]

Well I just want to be able to carry out the query in Access... as the sql express service is running on a client machine and i dont really want to be using sql express just to do one task....

 

[SQL_Hell]

where does the data reside then? in sql server or access?

 

[anilvaghela]

The data is in access.... what i have done is appended the table from access to ms sql.... then used the table in sql to work out that procedure....

IF there is away of having that data transferred back into access database then that would be good!

 

[SQL_Hell]

Yeah you could export the output from the stored procedure to access using SSIS.

But you code in the stored proc would need to be changed to:

Create PROC dbo.ShowHierarchy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @u_id int, @namesite varchar(50), @unique_id varchar(50)


set @unique_id = (Select unique_id from dbo.tbl_search Where u_id=@Root)
set @namesite = (Select namesite from dbo.tbl_search Where u_id =@Root)
[COLOR="Red"]
SELECT REPLICATE ('', @@NESTLEVEL *5) +@unique_id +',' +@namesite [/COLOR]

Set @u_id= (Select MIN(u_id) From dbo.tbl_search where parent_id =@Root)

While @u_id is not null
BEGIN
Exec dbo.ShowHierarchy @u_id
set @u_id =(Select MIN(u_id) FROM dbo.tbl_search WHERE parent_id = @Root and u_id > @u_id)
END
End
GO

To export the data, right click on your database in SSMS and select 'all tasks' then 'export data', a wizard will come up. When it asks you to select a data source select query and exec your proc here.

 

[SQL_Hell]

If you cant do this, or dont like it then you will need to code something in VBA, i dont think there are any access query functions that will do this type of query by default