How to execute a MS SQL Server Stored Procedure?

deleted_user — Tue, 29 Dec 2009 20:57:54 GMT

I have a stored procedure that creates a table. I want eg to execute the stored procedure so i can link the database table into eg.

How does one execute a stored procedure from EG?

Re: How to execute a MS SQL Server Stored Procedure?

deleted_user — Wed, 30 Dec 2009 00:20:28 GMT

I am still learning both SQL and re-aquainting myself with SAS via SAS EG/LE. I haven't had time to actually run this code but here is what my research seems to indicate.

I just got done looking at the "PROC SQL essentials code samples" available here on the website. It appears you mostly use the exact same code you would in an SQL editor window to execute the stored procedure.

Below is a sample of executing some SQL against an Oracle database that is located on the same pc as Enterprise Guide. Below that is guess at an example of code for a stored procedure and executing it in Ms SQL.

PROC SQL;
LIBNAME bluesky "C:\mydata\bluesky bookstore\";

CONNECT TO ORACLE
(user=scott password=tiger path=master);

SELECT *
FROM CONNECTION TO ORACLE

(SELECT currency "Currency", avg(exchrate) "Average",
max(exchrate) "Maximum", min(exchrate) "Minimum"
FROM bluesky.currency
WHERE currency IN ('CAD','GBP','DEM')
GROUP BY currency
);

DISCONNECT FROM ORACLE;

QUIT;
------------------------------snip-----------------------------------------------------
Here's the MSQL sample code I found outside of SAS.com code:

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Our Florida warehouse manager can then access inventory levels by issuing the command

EXECUTE sp_GetInventory 'FL'

The New York warehouse manager can use the same stored procedure to access that area's inventory.

EXECUTE sp_GetInventory 'NY'
---------------------------------------snip--------------------------------------------------------

So the code that you want appears to be:

PROC SQL;
LIBNAME bluesky "C:\mydata\bluesky bookstore\"; <- local database location I think

CONNECT TO MSQL <- this might not be the correct name for ms sql connector
(user=scott password=tiger path=master);

EXECUTE sp_GetInventory 'FL' <- whatever you called the stored procedure plus any parms you need to pass in.

DISCONNECT FROM MSQL;

QUIT;

-------------------------------------------snip-----------------------------------------------

Try it out, generate some errors and come back and tell us about them 🙂

Tom Miller

topic How to execute a MS SQL Server Stored Procedure? in SAS Enterprise Guide

How to execute a MS SQL Server Stored Procedure?

Re: How to execute a MS SQL Server Stored Procedure?