SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

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

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


Accepted Solutions
Solution
‎06-01-2015 02:57 AM
Super User
Posts: 5,424

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

Posted in reply to user24feb

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

Data never sleeps

View solution in original post


All Replies
Super User
Posts: 5,424

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

Ok, hear from you soon :-)

Data never sleeps
Contributor
Posts: 70

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

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

Super Contributor
Posts: 340

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

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;

Solution
‎06-01-2015 02:57 AM
Super User
Posts: 5,424

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

Posted in reply to user24feb

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

Data never sleeps
Contributor
Posts: 70

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

Posted in reply to user24feb

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 721 views
  • 3 likes
  • 3 in conversation