PBM : Add @@ObjectID as a parameter for ExecuteSQL() by aaronbertrand

Active

Type:	Suggestion
ID:	552345
Opened:	4/19/2010 5:41:50 AM
Access Restriction:	Public

Workaround(s)

view

There is no way to reference the object_id for the current context, though we can use @@ObjectName and @@SchemaName. Picture a case where you want to run custom SQL for each table (such as checking rowcount caps from sys.dm_db_partition_stats). You would create a condition based on a facet, using ExecuteSQL(), then to connect each table to the DMV you should be able to say:

ExecuteSQL('Numeric', 'SELECT ... WHERE [object_id] = @@ObjectID;')

Currently, because there is no @@ObjectID parameter available for substitution, the condition needs to be much more verbose and error-prone:

ExecuteSQL('Numeric', 'SELECT ... WHERE [object_id] = OBJECT_ID(@@SchemaName + ''.'' + @@ObjectName);')

Details (expand)

English

SQL Engine

Add a parameter @@ObjectID that allows for substitution of the actual object_id at runtime. This should be case-insensitive if possible (as some will type @@ObjectId). And since @ID is a known and documented property of several facets (such as table), @@ID should also be supported syntax.

Faster Development

Improved Administration

0 attachments

Posted by Microsoft on 12/13/2012 at 9:28 AM

We are closing this request as we do not have a plan to immediately work on it. It has been marked so that it can be resurrected if so needed.

Posted by Phil Brammer on 3/7/2011 at 8:34 AM

I opened a similar bug today, but I'd like to see the ability to pass any parameter specific to whichever facet you are working under available under ExecuteSql().

https://connect.microsoft.com/SQLServer/feedback/details/649944/pbm-enable-the-ability-to-pass-more-parameters-to-executesql

Posted by Microsoft on 6/23/2010 at 1:06 PM

We are looking at this for the next release. I do see the value to of adding this.

Microsoft