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

[jdmarshg]

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

[LinusH]

Ok, hear from you soon 🙂

[jdmarshg]

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

[user24feb]

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;

[LinusH · Accepted solution]

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 Pas...

[jdmarshg]

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 Pas...

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