sp_describe_undeclared_parameters and temp tables

Background

As a very quick follow-up to our last post, let us untie sp_describe_undeclared_parameters from Tableau.

SQL

Temporary Table

Code


use [tempdb]
go

set nocount on
go

set xact_abort on
go

declare @sql nvarchar(4000);

declare @CHAR_TAB char(1)

set @CHAR_TAB = char(9);

begin tran

    create table #sam
    (
        [id] bigint not null
            identity(1,1)
    )

    set @sql = N'select * from #sam '
                + ' where [id] = @P1'

    begin try

            print 'running sql '
            print '------------'
            print @sql

            print ''; print '';

            exec sp_describe_undeclared_parameters
                    @tsql = @sql           

        end try
        begin catch

           print 'Exception'
           print '========='

           print @CHAR_TAB
                    + 'ERROR_NUMBER() :- '
                    + cast
                       (ERROR_NUMBER()
                            as varchar(10)
                       )

           print @CHAR_TAB
                    + 'ERROR_MESSAGE() :- '
                    + ERROR_MESSAGE()

            print ''; print '';

        end catch

while (@@TRANCOUNT > 0)
begin

    rollback tran;

end

Output

Output – Image

temporaryTable.output.20190621.0910AM.PNG

Output – Textual


running sql
------------
select * from #sam  where [id] = @P1

Exception
=========
	ERROR_NUMBER() :- 208
	ERROR_MESSAGE() :- Invalid object name '#sam'.

Global Temporary Table

Code


use [tempdb]
go

set nocount on
go

set xact_abort on
go

declare @sql nvarchar(4000);

declare @CHAR_TAB char(1)

set @CHAR_TAB = char(9);

begin tran

    if object_id('##sam') is not null
    begin

        drop table ##sam

    end

    create table ##sam
    (
        [id] bigint not null
            identity(1,1)
    )

    set @sql = N'select * from ##sam '
                + ' where [id] = @P1'

    begin try

            print 'running sql '
            print '------------'
            print @sql

            print ''; print '';

            exec sp_describe_undeclared_parameters
                    @tsql = @sql           

        end try
        begin catch

           print 'Exception'
           print '========='

           print @CHAR_TAB
                    + 'ERROR_NUMBER() :- '
                    + cast
                       (ERROR_NUMBER()
                            as varchar(10)
                       )

           print @CHAR_TAB
                    + 'ERROR_MESSAGE() :- '
                    + ERROR_MESSAGE()

            print ''; print '';

        end catch

        if object_id('##sam') is not null
        begin

            drop table ##sam

        end

while (@@TRANCOUNT > 0)
begin

    rollback tran;

end

Output

Output – Image

temporaryTable.output.20190621.0910AM.PNG

Output – Textual


running sql
------------
select * from ##sam  where [id] = @P1

Output – Grid

globalTemporaryTable.output.20190621.0923AM

Source Code Control

GitHub

sp_describe_undeclared_parameters/TempTables
Link

Summary

It is provable that sp_describe_undeclared_parameters does not play well with temporary tables.

Yet, it does OK with global temporary tables.

 

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