locked
Getting NULL for User Defined Data Types RRS feed

  • Question

  • Hello All,

    I have a table RequestUtility with 3 columns Vendor, name, system. I want to call this table from BizTalk so that I send multiple vendors and will get corresponding Vendor, name and system for that. As I need to send multiple vendors at the same time I have created a User-Defined Table type VendorNames with a single column Names. and have written a SP :

    ALTER PROCEDURE [dbo].[GetName]
     -- Add the parameters for the stored procedure here
     @vendorN VendorNames ReadOnly
      
    AS

     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;

        -- Select statements for procedure here
     SELECT * from dbo.RequestUtility where Vendor IN (SELECT Names from @vendorN)

    This is working fine when the value for vendor is present in the table. But if the value is not present it is not returning any thing. But, my requirement is that for example vendor is Dummy which is not present in the table so it should return

    Vendor   Name  System

    Dummy  NULL   NULL

    But currently it is not returning anything.

    Friday, November 27, 2015 10:02 AM

Answers

  • Hi,

    Replace the query inside the stored procedure by:

    SELECT vn.Name as Vendor, ru.Name, ru.System 
    from @vendotN vn
    left join dbo.RequestUtility ru
    on vn.Names = ru.Vendor

    Hope it helps.

    Antonio Cesar


    Friday, November 27, 2015 10:19 AM

All replies

  • Hi,

    Replace the query inside the stored procedure by:

    SELECT vn.Name as Vendor, ru.Name, ru.System 
    from @vendotN vn
    left join dbo.RequestUtility ru
    on vn.Names = ru.Vendor

    Hope it helps.

    Antonio Cesar


    Friday, November 27, 2015 10:19 AM
  • Hi, Are you sure there is data in @vendorN table? Because with left join all data from @vendorN is returned, regardless RequestUtility has data or not. Antonio Cesar

    Friday, November 27, 2015 10:56 AM
  • @vendorN  is a User-defined table type so when am executing the SP I am inserting values to it.

    Declare @vendor VendorNames
    Insert @vendor (Names)
    values ('PGE'),('TXU')
    exec dbo.GetName


    Friday, November 27, 2015 10:58 AM
  • I thought @vendorN was a input table, because you have declared as ReadOnly. Antonio Cesar
    Friday, November 27, 2015 11:10 AM
  • Hi, In your code, when you call dbo.GetName, there is no parameter passed. The @vendor table is not passed through the SP. Antonio Cesar
    Friday, November 27, 2015 11:44 AM
  • Thanks Antonio your reply worked.. I was calling the SP incorrectly
    Friday, November 27, 2015 11:45 AM
  • @vendorN  is a User-defined table type so when am executing the SP I am inserting values to it.

    Declare @vendor VendorNames
    Insert @vendor (Names)
    values ('PGE'),('TXU')
    exec dbo.GetName


    Try below

    CREATE TYPE VendorNames AS TABLE 
    ( Names VARCHAR(50));
    GO
    Create table RequestUtility
    (id int identity(1,1) ,
    Names varchar(50)
    )
    Insert RequestUtility (Names)
    values ('PGE'),('TXU'),('Dummy')
    Create PROCEDURE [dbo].[GetName] 
     -- Add the parameters for the stored procedure here
     @vendorN VendorNames ReadOnly
    AS
    SET NOCOUNT ON;
    SELECT vn.Names as Vendor, vn.id,ru.Names
    from dbo.RequestUtility vn
    left join @vendorN ru
    on vn.Names = ru.names
    Declare @vendor VendorNames
    Insert @vendor (Names)
    values ('PGE'),('TXU')
    exec dbo.GetName @vendorN = @vendor


    Thanks Saravana Kumar C


    • Edited by SaravanaC Friday, November 27, 2015 11:57 AM
    Friday, November 27, 2015 11:56 AM