Converting SQL SP to an Access Query

[golferdude1971]

I use MS-SQL 100% of the time, and I have a project that I have to convert from SQL to Access. I have little knowledge about Access queries, and I was wondering if the stored procedure below would work in Access


CREATE PROCEDURE MoveItems
(
@direction int,
@RID_IN int
)
AS



/*
declare @direction int
select @direction = 1 --up 1 // down 2
declare @RID_IN int
select @RID_IN = 7
*/


declare @MaxORder int
declare @MinORder int

select @MinORder = min(listorder) from gnt where status = 1
select @MaxORder = max(listorder) from gnt where status = 1

declare @prevorder int
select @prevorder = listorder from gnt where rid = @RID_IN and status = 1

declare @replaceRID int
declare @replaceRID_Order int


if @direction = 1
begin
if @prevorder > @MinORder
begin
select @replaceRID = (select top 1 rid from gnt where @prevorder > listorder and status = 1 order by listorder desc)
select @replaceRID_Order = listorder from gnt where rid = @replaceRID and status = 1


/*
print '@MinORder =' + cast(@MinORder as varchar)
print '@MaxORder=' + cast(@MaxORder as varchar)

print '@prevorder =' + cast(@prevorder as varchar)
print '@replaceRID=' + cast(@replaceRID as varchar)
print '@replaceRID_Order=' + cast(@replaceRID_Order as varchar)
*/

update gnt
set listorder = @replaceRID_Order
where rid = @RID_IN

update gnt
set listorder = @prevorder
where rid = @replaceRID



end
end


if @direction = 2
begin
if @prevorder < @MaxORder
begin
select @replaceRID = (select top 1 rid from gnt where @prevorder < listorder and status = 1 order by listorder )
select @replaceRID_Order = listorder from gnt where rid = @replaceRID and status = 1

/*
print '@prevorder =' + cast(@prevorder as varchar)
print '@replaceRID=' + cast(@replaceRID as varchar)
print '@replaceRID_Order=' + cast(@replaceRID_Order as varchar)
*/

update gnt
set listorder = @replaceRID_Order
where rid = @RID_IN

update gnt
set listorder = @prevorder
where rid = @replaceRID


end
end
GO

 

[Quehay]

Access has no procedural engine like T-SQL. You'd have to do the procedural and variable-related stuff in VBA with ADO or DAO for data connectivity. But you can do it...


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[golferdude1971]

Quehay,
Thanks for your response. The front end is to be asp, which would involve alot of validation prior to executing some sort of query, and this is what Im trying to avoid(reinventing the wheel). Thanks for you input.

 

[Quehay]

If you do the validation in JavaScript you'll avoid the extra server trip for validation...Access 2002 supports triggers (I don't have it) so this might be server-side code that you could create if you've got 2002. Queries can be stored on the db and called as stored procedures using ADO command, but I'm not aware of how you could call the procedural stuff there--I'll look it up though--it's a good question...


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com