Help explain what the code doing

[Mike_Davis]

Hello everyone,

Does anybody could explain the meaning of code to me?

Thank you!

proc sql;

connect to odbc as xyzp (dsn=asdf user=wind pw=cool);

execute (exec [xyz].[dbo].[sp_report_indexCreate]) by xyzp;

quit;

[Nigel_Pain]

Mike

This executes a stored procedure on what looks like a SQL Server database using SAS/Access for ODBC.

The connect statement establishes a connection to the database: the details of this are in the dsn (Data Source Name) specified - this would specify the server, port and database names (use your ODBC manager software to see these) and the user and pw options specify the authentication credentials. The "as xyzp" part establishes an alias: without it you would have to replace any references to "xyzp" with "odbc". You could conceivably have several ODBC connections on the go and so you would need to be able to differentiate between them when passing code to them. But if you only have the one connection the alias isn't strictly necessary. The execute statement passes the contents of the parentheses directly to the database to run itself. Any output would be passed back to SAS.

One thing that the code is missing is a disconnect statement: this should go between the execute and quit statements and consist of:

disconnect from xyzp;

I'm afraid I'm not very up on SQL Server so I can't really help with the details of the stored procedure that is run in this case. I think the [dbo] bit specifies the schema and the [sp_report_indexCreate] specifies the procedure name but I don't know what the [xyz] bit is.

Hope this helps,

Nigel

[Mike_Davis]

Thank you very much.

for sql server,using T-SQL

here is an example of  create a stored procedure one with a parameter x :

CREATE PROCEDURE one @x char(10)
AS
SELECT *
FROM sashelp.class

WHERE x= @x
go

To call this stored procedure one :

EXEC one @x = 'M''