Home Dashboard Directory Help
Search

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


Status: 

Active


3
0
Sign in
to vote
Type: Suggestion
ID: 552345
Opened: 4/19/2010 5:41:50 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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
Sign in to post a comment.
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.
Sign in to post a workaround.