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?