Stored procedure returns Null when insert

Question

I have a stored procedure for inserting data into a table in SQL Server 2008.

Here's my stored procedure :

ALTER PROCEDURE [dbo].[RegisterOnline_Insert]
    --@OrderType int,
    @SiteID as varchar(50),
    @CustName as varchar(100),
    @Address as varchar(255),
    @Phone as varchar(20),
    @HP as varchar(20),
    @Email as varchar(100),
    @Description as varchar(500),
    @IsValid as bit


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

DECLARE @House as numeric(10,0)
    DECLARE @Fibernode as varchar(50)
    DECLARE @ftax as numeric(5,0)
    DECLARE @Demographics2 AS VARCHAR(50)
    DECLARE @PostalCode as varchar(10)
    DECLARE @InputBy as varchar(50)
    DECLARE @addr as varchar(max)
    DECLARE @addrid as int


    SET @InputBy = '[Website]'
    SET @addr = dbo.udf_StripHTML(@Address)

    SELECT @PostalCode = postal_code, 
           @ftax = ftax,
           @Fibernode = fibernode,
           @Demographics2 = demographics2,
           @addrid = Address_id
    FROM dbo.TEMP_HOUSE_INFO2_SITE
    WHERE site_id = @SiteID 


    SET NOCOUNT ON;

        INSERT INTO tbMynetOrder(
                OrderType,
                SITE_ID ,
                AddressID,
                House,
                Cust,
                CustName,
                Address,
                PostalCode,
                Fibernode,
                PhoneOld,
                Phone,
                HPOld,
                HP,
                Email,
                ftax,
                BoD,
                ContactPerson,
                IDCard,
                PaymentMethod,
                Description,
                SalesCode,
                SchedDate,
                SchedTimeID,
                Complete,
                InstallationCost,
                Source,
                Covered,
                CCIssueBy,
                CCType,
                CCNo,
                CCExpireDate,
                CCHolderName,
                CCHolderPhone,
                CCFrequency,
                CCPromoService,
                CCAmount,
                Demographics2,
                SCA,
                BlackList,
                InputBy,
                InputDate       
            )
            VALUES(
                1, -- Order Type
                @SiteID,
                ISNULL(@addrid,0), -- Site ID
                null, -- House
                null, -- Cust
                UPPER(@CustName), -- Customer Name
                @addr, -- Address
                @PostalCode, -- PostalCode
                @Fibernode, -- FiberNode
                @Phone, -- PhoneOld
                null, -- Phone
                @HP, -- HP Old
                @HP, -- HP
                @Email, -- Email
                @ftax, -- ftax
                null, -- BOD
                null, -- Contact Person
                null, -- ID Card
                null, -- Payment Method
                @Description, -- Description
                null, -- Sales Code
                null, --SchedDate
                null, -- SchedTimeID
                null, -- Complete
                null, -- InstallationCost
                3, -- Source , 3 = Website
                @IsValid, -- Covered
                null, -- CCIssueBy
                null, -- CCType
                null, -- CCNo
                null, -- CCExpireDate
                null, -- CCHolderName
                null, -- CCHolderPhone
                null, -- Frequency
                null, -- CCPromo
                null, -- CCAmount
                @Demographics2, -- Demographics2
                null, -- SCA
                null, -- Blacklist
                @InputBy, -- Inputby
                GETDATE() -- Input Date

            )    

END

I have a problem with my @SiteID parameter value, when it process to get some value, it has the value and give the result :

SELECT @PostalCode = postal_code, 
               @ftax = ftax,
               @Fibernode = fibernode,
               @Demographics2 = demographics2,
               @addrid = Address_id
        FROM dbo.TEMP_HOUSE_INFO2_SITE
        WHERE site_id = @SiteID 

but when insert into table, the value became null, why it happens? is it something wrong with my insert statement?

Answer 1

try this

when SET NOCOUNT ON; then SP not return value Becouse of this your command.executenonqeury require result other wise it roll back operation so after insert statement write select scope_identity() return last inserted PK

after insert write

if you write select scope_identity()

select scope_identity()

now your processure like this

ALTER PROCEDURE [dbo].[RegisterOnline_Insert]
    --@OrderType int,
    @SiteID as varchar(50),
    @CustName as varchar(100),
    @Address as varchar(255),
    @Phone as varchar(20),
    @HP as varchar(20),
    @Email as varchar(100),
    @Description as varchar(500),
    @IsValid as bit


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

DECLARE @House as numeric(10,0)
    DECLARE @Fibernode as varchar(50)
    DECLARE @ftax as numeric(5,0)
    DECLARE @Demographics2 AS VARCHAR(50)
    DECLARE @PostalCode as varchar(10)
    DECLARE @InputBy as varchar(50)
    DECLARE @addr as varchar(max)
    DECLARE @addrid as int


    SET @InputBy = '[Website]'
    SET @addr = dbo.udf_StripHTML(@Address)

    SELECT @PostalCode = postal_code, 
           @ftax = ftax,
           @Fibernode = fibernode,
           @Demographics2 = demographics2,
           @addrid = Address_id
    FROM dbo.TEMP_HOUSE_INFO2_SITE
    WHERE site_id = @SiteID 


    SET NOCOUNT ON;

        INSERT INTO tbMynetOrder(
                OrderType,
                SITE_ID ,
                AddressID,
                House,
                Cust,
                CustName,
                Address,
                PostalCode,
                Fibernode,
                PhoneOld,
                Phone,
                HPOld,
                HP,
                Email,
                ftax,
                BoD,
                ContactPerson,
                IDCard,
                PaymentMethod,
                Description,
                SalesCode,
                SchedDate,
                SchedTimeID,
                Complete,
                InstallationCost,
                Source,
                Covered,
                CCIssueBy,
                CCType,
                CCNo,
                CCExpireDate,
                CCHolderName,
                CCHolderPhone,
                CCFrequency,
                CCPromoService,
                CCAmount,
                Demographics2,
                SCA,
                BlackList,
                InputBy,
                InputDate       
            )
            VALUES(
                1, -- Order Type
                @SiteID,
                ISNULL(@addrid,0), -- Site ID
                null, -- House
                null, -- Cust
                UPPER(@CustName), -- Customer Name
                @addr, -- Address
                @PostalCode, -- PostalCode
                @Fibernode, -- FiberNode
                @Phone, -- PhoneOld
                null, -- Phone
                @HP, -- HP Old
                @HP, -- HP
                @Email, -- Email
                @ftax, -- ftax
                null, -- BOD
                null, -- Contact Person
                null, -- ID Card
                null, -- Payment Method
                @Description, -- Description
                null, -- Sales Code
                null, --SchedDate
                null, -- SchedTimeID
                null, -- Complete
                null, -- InstallationCost
                3, -- Source , 3 = Website
                @IsValid, -- Covered
                null, -- CCIssueBy
                null, -- CCType
                null, -- CCNo
                null, -- CCExpireDate
                null, -- CCHolderName
                null, -- CCHolderPhone
                null, -- Frequency
                null, -- CCPromo
                null, -- CCAmount
                @Demographics2, -- Demographics2
                null, -- SCA
                null, -- Blacklist
                @InputBy, -- Inputby
                GETDATE() -- Input Date

            )    
select scope_identity()
END