using if object_id in SQL server via SAS

Question

I am running some SQL queries via SAS using the credentials below.

The Drop Table command on it's own is fine, creating the tables is fine. I get an error with the "if object_id" line.

ERROR: Execute error: ICommand::Execute failed. : Invalid column name 'scratch.dbo.eof_tv_channel_orders_fy16_NEW_t'

I am assuming it does not like the SAS macro variable in the quotes (I tried single too).

Is there some other SQL command I could use? I could do a proc delete in SAS outside the SQL but would rather not.

%let test_flag=t;

PROC SQL;
CONNECT TO OLEDB (
  CURSOR_TYPE=STATIC 
  BULKLOAD=YES 
  READ_ISOLATION_LEVEL=RU 
  PROPERTIES=("Integrated Security"=SSPI)  
  PROVIDER=SQLOLEDB.1  
  DATASOURCE="XXXXXXX"
);


EXECUTE (


if object_id ("scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag.", 'u') is not null
  drop table scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag.

) by OLEDB;

quit;

Take a look at the BQUOTE suggestion here: stackoverflow.com/questions/31714447/… — Stig Eide
– Stig Eide, Commented Aug 4, 2015 at 10:37

Pekka · Accepted Answer · 2015-08-04 10:53:35Z

1

The problem is that you are using double quotes instead of single quotes. In SQL server (and in most SQL flavors) double quotes don't mean character string.

For object_id function character string is required for the first parameter.

To overcome this problem you must use single quotes. However when you use macro-variable in single quotes it won't be resolved.

So you have to do this:

object_id(%str(%'scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag%'), 'u')

Then it will be resolved to:

object_id('scratch.dbo.eof_tv_channel_orders_fy16_NEW_t', 'u')

and it should work.

E: as Stig Eide suggested also this can be used and it is equivalent to the %str(... method above:

object_id(%BQUOTE('scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag'), 'u')

You have to choose by yourself which syntax looks better.

edited Aug 4, 2015 at 10:53

answered Aug 4, 2015 at 10:39

Pekka

2,4582 gold badges23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using if object_id in SQL server via SAS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related