SQL Server passing identifiers to stored procedures/dynamic SQL

Question

Background:

SQL Server Management Studio allows to define own query shortcuts (Tools > Options > Environment > Keyboard > Query Shortcuts):

_{Image from: http://social.technet.microsoft.com/wiki/contents/articles/3178.how-to-create-query-shortcuts-in-sql-server-management-studio.aspx}

my_schema.my_table
-- highlight it
-- press CTRL + 3 and you will get the number of rows in table

It works ok, but it concatenates query in basic form (as far as I know only at the end). Query:

SELECT COUNT(*) FROM my_schema.my_table;

---

Attempt #1

Now I want to write something more specific, for example pass/concatenate table name to following query (this is just example):

SELECT * FROM sys.columns WHERE [object_id] = OBJECT_ID(...)

So when I write in query shortcuts:

SELECT * FROM sys.columns WHERE [object_id] = OBJECT_ID('

I have to use:

my_schema.my_table')
-- highlight it
-- press CTRL + 3

The additional ') is very ugly and inconvenient.

Attempt #2:

The second trial is to use Dynamic-SQL:

EXEC dbo.sp_executesql
      N'SELECT * FROM sys.columns WHERE [object_id] = OBJECT_ID(@obj_name)'
     ,N'@obj_name SYSNAME'
     ,

Executing:

 my_table
 -- highligt it 
 -- and run 

LiveDemo

Works also when table name is quoted [my_table]. As long as object is in dbo(default) schema.

The problem is that when table has schema it won't work:

EXEC dbo.sp_executesql
      N'SELECT * FROM sys.columns WHERE [object_id] = OBJECT_ID(@obj_name)'
     ,N'@obj_name SYSNAME'
     ,

Executing:

my_schema.my_table
[my_schema].[my_table]

LiveDemo2

Incorrect syntax near '.'.

Of course I could write:

EXEC dbo.sp_executesql
      N'SELECT * FROM sys.columns WHERE [object_id] = OBJECT_ID(@obj_name)'
     ,N'@obj_name SYSNAME'
     ,'

and call it as:

 [my_schema].[my_table]'

But additional ' is also ugly and inconvenient.

Questions:

1. Is it possible to pass value, to query shortcuts window, in the middle (positional or even more than one value)?

2. Is it possible to pass do stored_procedure/dynamic-sql qualified identifier without wraping it with ', "?

Remarks:

• I do not search for plugins to SSMS
• I do not want to wrap object_name as "my_schema.my_table"
• I know there is sp_helptext (this is just example, I search for method)
• First question is tool specific (I am aware of it), but second is about SQL Server.

EDIT:

To clarify passing identifier to SP without ' or ":

CREATE TABLE dbo.my_table(col INT);
GO
    
CREATE PROCEDURE dbo.my_proc
  @a SYSNAME
AS
SELECT *
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@a)
GO

EXEC dbo.my_proc
   @a = my_table;

EXEC dbo.my_proc
   @a = dbo.my_table;
-- Incorrect syntax near '.'.

LiveDemo3

Answer 1

1. Is it possible to pass value, to query shortcuts window, in the middle?

To my knowledge, there is no workaround to achieve this.

1-b. Is it possible to pass more than one value?

It can be done for string values using a separator character and then splitting the value on the other side. Sadly, there isn't many special character to fulfill this job because they pretty much all raise a syntax error. However '#' could be a wise choice because it's already a special character for SQL for temp table going in tempDB. Just check if you don't already have identifier that are using it because it's permitted by SQL (tough, it's forbidden as first char).

Here is an example of this :
Create a stored procedure to receive the arguments into one single string and split the string to have each arguments.

    CREATE PROCEDURE sp_PassingMultipleStringValues 
        @Param1 NVARCHAR(MAX)
    AS
    
    --Here I'm using a XML split, but feel free to use any string split function you already have.
    DECLARE @xml        AS XML,
            @separator  AS VARCHAR(1)
    
    SELECT  @separator ='#',
            @xml = CAST('<X>'+ (REPLACE(@Param1,@separator ,'</X><X>') +'</X>') AS XML)
    
    SELECT N.value('.', 'VARCHAR(200)') AS value 
    FROM @xml.nodes('X') as T(N)
    --Do whatever is needed with them

Then configure your shortcut as seem on this image. (Note the space at the end)

Result :

2. Is it possible to pass to a stored_procedure/dynamic-sql qualified identifier without wraping it with ', "?

Do you have multiple schema with the same identifier?
Because if not, what about retrieve it on the other side using sys.schemas instead of passing it?
Instead of having an inconvenient character to type at the end, you would have fewer things to type.

With the retrieved schema, you can then do dynamic SQL for whatever is needed with it.

    SELECT @Param1 = REPLACE(REPLACE(@Param1, '[', ''), ']', '')
    
    SELECT TOP 1 @Param1 = [Schema].name + '.' + @Param1
    FROM     sys.objects AS obj
    JOIN     sys.schemas AS [Schema] ON obj.schema_id = [Schema].schema_id
    WHERE    obj.name = @Param1
    
    SELECT * 
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@Param1)

    DECLARE @Query NVARCHAR(MAX) = 'SELECT TOP 1 * FROM ' + @Param1
    EXEC sp_sqlexec @Query

If you do want to handle two different schema with the same identifier then it's still feasible by passing the schema and the identifier as two arguments using the method explained in answer 1-b.

Everything in one example

Since here we want to pass multiple identifiers and specify their schema, two separators are needed.

CREATE PROCEDURE sp_MultiArgsWithSchema
    @Param1 NVARCHAR(MAX)
AS

SELECT @Param1 = REPLACE(REPLACE(@Param1, '[', ''), ']', '')

--Here I'm using a XML split, but feel free to use any string split function you already have.
DECLARE @xml            AS XML,
        @ArgSeparator   AS VARCHAR(2),
        @SchemaSeparor  AS VARCHAR(1)

SELECT  @ArgSeparator = '##',
        @SchemaSeparor = '#',
        @xml = CAST('<X>'+ (REPLACE(@Param1,@ArgSeparator, '</X><X>') +'</X>') AS XML)

IF OBJECT_ID('tempdb..#QualifiedIdentifiers') IS NOT NULL 
    DROP TABLE #QualifiedIdentifiers;

--While splitting, we are putting back the dot instead of '#' between schema and name of object
SELECT QualifiedIdentifier = REPLACE(N.value('.', 'VARCHAR(200)'), @SchemaSeparor, '.') 
INTO #QualifiedIdentifiers
FROM @xml.nodes('X') as T(N)

SELECT * FROM #QualifiedIdentifiers

--From here, use what is inside #QualifiedIdentifiers and Dynamic SQL if need to achieve what is needed
DECLARE @QualifiedIdentifier    NVARCHAR(500)
WHILE EXISTS(SELECT TOP 1 1 FROM #QualifiedIdentifiers)
BEGIN
    SELECT TOP 1 @QualifiedIdentifier = QualifiedIdentifier
    FROM #QualifiedIdentifiers

    SELECT * 
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@QualifiedIdentifier)
 
    DELETE TOP (1) 
    FROM #QualifiedIdentifiers
    WHERE QualifiedIdentifier = @QualifiedIdentifier
END

Usage (note that specifying the schema isn't mandatory) :

So, since it is inconvenient to have to double the splitting character, it would be best if schema could be guessed like stated above.