Must declare the scalar variable @bundleTransfer when it's a table type?

32 views Asked by At

Summary

I'm trying to use a user-defined table type and return it from a table-valued function.

Currently using SQL Server 2008 and its XML queries.

Sample

create type udtBundleTransferTableType table as (
    C_PPPI_BATCH              varchar(10)   not null,
    C_PPPI_SO_ISSUE           varchar(10)   not null,
    C_PPPI_SO_RECEIPT         varchar(10)   not null,
    C_PPPI_MATERIAL           varchar(18)   not null,
    C_PPPI_MATERIAL_CONSUMED  decimal(11,4) not null,
    [TIMESTAMP]               datetime      not null default getDate()
)
go

create function dbo.udfReadBundleTransferInformationReceived(@bundleTransferMessage xml)
    returns udtBundleTransferTableType as
begin
    declare @bundleTransferInformation udtBundleTransferTableType
    insert into @bundleTranserInformation(C_PPPI_BATCH
                                        , C_PPPI_SO_ISSUE
                                        , C_PPPI_SO_RECEIPT
                                        , C_PPPI_MATERIAL
                                        , C_PPPI_MATERIAL_CONSUMED)
        select bundle.transfer.value('C_PPPI_BATCH[1]', 'varchar(10)') as C_PPPI_BATCH
                , bundle.transfer.value('C_PPPI_SO_ISSUE[1]', 'varchar(10)') as C_PPPI_SO_ISSUE
                , bundle.transfer.value('C_PPPI_SO_RECEIPT[1]', 'varchar(10)') as C_PPPI_SO_RECEIPT
                , bundle.transfer.value('C_PPPI_MATERIAL[1]', 'varchar(18)') as C_PPPI_MATERIAL
                , bundle.transfer.value('C_PPPI_MATERIAL_CONSUMED[1]', 'decimal(11,4)') as C_PPPI_MATERIAL_CONSUMED
                , bundle.transfer.value('TIMESTAMP[1]', 'datetime') as [TIMESTAMP]
            from @bundleTransferMessage.nodes('/BundleTransferMessage/Characteristics') bundle(transfer)

    return @bundleTransferInformation
end

Error

Msg 137, Level 16, State 1, Procedure udfReadBundleTransferInformationReceived, Line 35 [Batch Start Line 0] Must declare the scalar variable @bundleTransferInformation

Question

What's happening?

I don't understand why SSMS says that I must declare the scalar variable, because it's not a scalar variable, it's a table variable!

How can I declare it in a way that SSMS/SQL Server will interpret it as expected?

What am I doing wrong?

0

There are 0 answers