I have an Access DB and I would like to migrate it to SQL Server. My problem is that there are a lot of macros in the Access database, each of which includes several different queries. How to create a stored procedure to execute several different queries? I would appreciate a detailed explanation.
hope this helps
you can create a separate procedure for each query
you can put all the queries in one stored procedure with a parameter
depending on the parameter the query runs
create procedure XYZ ( @queryID int ) as begin if @queryid = 1 then select1 if @queryid = 2 then select2 end exec XYZ @queryID=1
First of all: not all macro's need to be converted to SQL procedures. Just make a analysis which processes are best to run in the background, and which procedures can best be kept in the front end.
Also review your queries and procedures: avoid using RBAR (loops, cursors) on a database. When an access procedure contains several SQL statements, perhaps you can rewrite the VBA procedure using temp tables on SQL server combining all statements in one SQL procedure/statement. So basically I would rethink the procedures you want to move to SQL server, to rewrite them in a more performant way, fit for the way the server works.