Transact SQL :- sp_describe_undeclared_parameters

Background

One of our Reporting tools is failing.

Wanted to see why …

SQL

The failing SQL looks something like this :-


exec sp_describe_undeclared_parameters 
   N'INSERT INTO [#Connect] ([COL])
    VALUES (@P1)'

sp_describe_undeclared_parameters ?

What is sp_describe_undeclared_parameters ?

Well it allows laziness.

Have a SQL in mind and you do not want to issue sp_help against each table and determine the column types and size.

sp_describe_undeclared_parameters

Syntax

sp_describe_undeclared_parameters 
     @tsql = {'Transact-SQL_batch'}   

Sample

Sample – HP/ALM

Sample – HP/ALM – td.Projects
Query – Ask for column definition
sp_describe_undeclared_parameters @tsql =   
N'
    SELECT top 10 * 
    from [qcsiteadmin_1250_db].[td].[PROJECTS] 
    where [PROJECT_NAME] = @project
'  

Actual SQL

declare @project varchar(255)

set @project = 'BIS'
SELECT top 10 * 
from [qcsiteadmin_1250_db].[td].[PROJECTS] 
where [PROJECT_NAME] = @project

Sample – Jira

Sample – Jira – table – jiraissue
sp_describe_undeclared_parameters @tsql =   
N'
    SELECT top 100 * 
    from   [d_jiradb].[dbo].[jiraissue]
    where  [REPORTER] = @reporter
    and    [ASSIGNEE] = @assignee
    and    [CREATOR] =  @creator
    and    [CREATED] 
             between @dateCreatedBegin 
             and @dateCreatedCompleted
'  

Output

Sample – Jira – table – jiraissue – Actual

declare @reporter               nvarchar(255)
declare @assignee               nvarchar(255)
declare @creator                nvarchar(255)
declare @dateCreatedBegin       datetime
declare @dateCreatedCompleted   datetime

set @dateCreatedBegin = '2018-10-01'

set @dateCreatedCompleted = '2019-03-01'

SELECT top 100 * 
    from   [d_jiradb].[dbo].[jiraissue]
    where  [REPORTER] = isNull(@reporter, [REPORTER])
    and    [ASSIGNEE] = isNull(@assignee, [ASSIGNEE])
    and    [CREATOR] =  isNull(@creator, [CREATOR] )
    and    [CREATED] 
                between @dateCreatedBegin 
                and @dateCreatedCompleted

SQL Server Version

sp_describe_undeclared_parameters was introduced in MS SQL Server v2012.

References

  1. Docs / SQL / Reference / System stored procedures / Database Engine / sp_describe_undeclared_parameters
    • sp_describe_undeclared_parameters (Transact-SQL)
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s