topic Re: Data Integration Studio (Pass sas macro variable into SQL Server Stored Procedure in SAS Data Management

Data Integration Studio (Pass sas macro variable into SQL Server Stored Procedure

jdmarshg — Fri, 29 May 2015 11:49:19 GMT

Good morning everyone,

I have learned how to call a stored procedure in stored procedure.

I have still yet to be able to using SQL Pass Thru if the correct approach to pass a macro variable into a stored procedure residing in SQL Server.

Will post more code shortly as I continue to research and try different methods.

Regards,

Jonathan Marshall

Re: Data Integration Studio (Pass sas macro variable into SQL Server Stored Procedure

LinusH — Fri, 29 May 2015 12:11:13 GMT

Ok, hear from you soon 🙂

Re: Data Integration Studio (Pass sas macro variable into SQL Server Stored Procedure

jdmarshg — Mon, 01 Jun 2015 02:49:21 GMT

I have used this code to call a job within sql server with a hard coded value for the parameter job_name.

I'm looking to take a macro variable and pass it into the sql server stored procedure.

How would I go about doing that?

proc sql;

connect to SQLSVR as SQLDB

(datasrc="&datasrc");

select * from connection to SQLDB

/*( EXECUTE msdb.dbo.sp_start_job @job_id = 'E9B5D67A-0695-49F1-8CE2-388671ECB4B6');*/

( EXECUTE msdb.dbo.sp_start_job @job_name = 'job_name');

disconnect from SQLDB;

quit;

Regards,

Jonathan

Re: Data Integration Studio (Pass sas macro variable into SQL Server Stored Procedure

user24feb — Mon, 01 Jun 2015 06:39:42 GMT

I think the explicit pass-through and the macro-variable don't mix. Try something like this:

%Let datasrc=Macro_datasrc; *!;
%Let job_name=Macro_job_name;

Data Command;
Length Cmd $100.;
Cmd="Proc SQL;"; Output;
Cmd=Catt("   connect to SQLSVR as SQLDB (datasrc=","&datasrc.",");"); Output;
Cmd=Catt("   select * from connection to SQLDB ( EXECUTE msdb.dbo.sp_start_job @job_name = '","&job_name.","');"); Output;
Cmd="   disconnect from SQLDB;"; Output;
Cmd="Quit;"; Output;
Run;

Data _NULL_;
Set Command;
Call Execute (Cmd);
Run;

Re: Data Integration Studio (Pass sas macro variable into SQL Server Stored Procedure

LinusH — Mon, 01 Jun 2015 06:57:12 GMT

Yes they do mix, but you need to take into count differences in quoting syntax.

If you need single quotes around your job I'd you might get some help from this usage note:

13485 - How to pass a macro variable into the WHERE clause to query an Oracletable with PROC SQL Pass-Through

Re: Data Integration Studio (Pass sas macro variable into SQL Server Stored Procedure

jdmarshg — Mon, 01 Jun 2015 18:08:09 GMT

OOOOH Yes....

It tried the approach Linus placed forth..

In so many searches why have I not found this link... 13485 - How to pass a macro variable into the WHERE clause to query an Oracletable with PROC SQL Pass-Through

I just got it to work.... I'm coming from the SSIS world so of course SSIS to SQL Server is merely built in but this case just opens soo many doors with integration with SQL Server.

I'm now going to try user24feb approach

This made my day ....

many thanks

%let dataset_name = Jonathan;

proc sql;

connect to SQLSVR as SQLDB

(datasrc="DEV04Kerberos");

select * from connection to SQLDB

(EXECUTE Jonathan.dbo.InsertNewSchema @SchemaName = %unquote(%str(%'&dataset_name%')));

disconnect from SQLDB;

quit;

Regards,

Jonathan