1

I'm splitting three columns into multiple multiple addresses (each column has more than 1 value separated by the ","). I've written the code below, but it throws me and error:

Must declare the scalar variable '@tblAddresses'.

I does work coping everything to the '@tblAddresses' and even to the temp table, but as soon as I execute the entire code the error comes up.

DECLARE @tblAddresses TABLE
 ([EBN] INT, [Address (as text)] VARCHAR(4000), [Address (main)] VARCHAR(4000), [Side Address] VARCHAR(4000));

INSERT INTO @tblAddresses
        SELECT [EBN], [Address (as text)], [Address (main)],  [Side Address] FROM  [dbo].[ImportDestination]
    ---SELECT * INTO #ttAddresses FROM @tblAddresses -- I was trying with temp tbl as well changing @tblAddresses to #ttAddresseses in the CTE FROM clause column, but did not help 

;WITH AddressesCTE 
AS 
 ( 
 SELECT *
        ,CAST('<x>' + REPLACE(@tblAddresses,',','</x><x>') + '</x>' AS XML) AS AsXmlAddress

 FROM @tblAddresses
 )
 SELECT [EBN]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[1]','nvarchar(max)'))) AS [AddressText1]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[2]','nvarchar(max)'))) AS [AddressText2]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[3]','nvarchar(max)'))) AS [AddressText3]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[4]','nvarchar(max)'))) AS [AddressText4]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[5]','nvarchar(max)'))) AS [AddressText5]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[1]','nvarchar(max)'))) AS [AddressText6]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[7]','nvarchar(max)'))) AS [AddressMain1]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[8]','nvarchar(max)'))) AS [AddressMain2]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[9]','nvarchar(max)'))) AS [AddressMain3]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[10]','nvarchar(max)'))) AS [AddressMain4]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[11]','nvarchar(max)'))) AS [AddressMain5]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[12]','nvarchar(max)'))) AS [AddressMain6]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[13]','nvarchar(max)'))) AS [AddressMain7]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[14]','nvarchar(max)'))) AS [AddressSide1]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[15]','nvarchar(max)'))) AS [AddressSide2]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[16]','nvarchar(max)'))) AS [AddressSide3]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[17]','nvarchar(max)'))) AS [AddressSide4]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[18]','nvarchar(max)'))) AS [AddressSide5]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[19]','nvarchar(max)'))) AS [AddressSide6]
      ,LTRIM(RTRIM(AsXmlAddress.value('/x[20]','nvarchar(max)'))) AS [AddressSide7]
 FROM AddressesCTE
Data Engineer
  • 795
  • 16
  • 41
  • What `REPLACE(@tblAddresses,',','')` is supposed to do? – PM 77-1 Oct 08 '16 at 01:06
  • split the addresses separated by comma within a single column – Data Engineer Oct 08 '16 at 01:14
  • Is `@tblAddresses` a column? – PM 77-1 Oct 08 '16 at 01:18
  • it's a table variable. This post may help with the XML portion of the code above. http://stackoverflow.com/questions/39883431/split-single-column-into-multiple-and-load-it-to-a-table-or-a-view – Data Engineer Oct 08 '16 at 01:20
  • 1
    Please go back to the example you provided and compare with your code. Especially, the `REPLACE` part. You should see the difference and that will be exactly your problem. – PM 77-1 Oct 08 '16 at 01:24
  • Thanks for pointing it out. My tblAddresses is a table variable containing more than 1 column, so REPLACE function cannot handle it as it takes only 3 parameters. I replaced it with a single address column name from the @tblAddresses definition and it worked – Data Engineer Oct 08 '16 at 04:14

0 Answers0