Sabilv Sabilv - 1 month ago 8
Vb.net Question

Catch the sp_executesql table Result

I Have Stored Procedure by using sp_executesql:

USE [databasedevelopment]
GO
/****** Object: StoredProcedure [dbo].[SearchPaymentDev] Script Date: 06/03/2013 16:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[SearchPaymentDev]

@PayAccountin as varchar(10),
@PayCustNamein as varchar(30),
@PayAmountin as int,
@PayAmountPaidin as int,
@PayResponsein as char (2),
@PayRefNoin as varchar (120),
@PayScreenTextin as varchar (100),
@PayReceiptTextin as varchar (350),
@PayDatetimein as varchar(50),
@PayBankCodein as varchar (6)

AS
Set NoCount ON

Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

Set @SQLQuery = 'Select * From payment where PayId is not null '

If @PayAccountin Is Not Null
Set @SQLQuery = @SQLQuery + ' And (PayAccount LIKE ''%'' + @PayAccountin + ''%'')'

If @PayCustNamein Is Not Null
Set @SQLQuery = @SQLQuery + ' And (PayCustName LIKE ''%'' + @PayCustNamein + ''%'')'

If @PayAmountin Is Not Null
Set @SQLQuery = @SQLQuery + ' And (PayAmount LIKE ''%'' + @PayAmountin + ''%'')'

If @PayAmountPaidin Is Not Null
Set @SQLQuery = @SQLQuery + ' And (PayAmountPaid LIKE ''%'' + @PayAmountPaidin + ''%'')'

If @PayResponsein is Not Null
Set @SQLQuery = @SQLQuery + ' And (PayResponse LIKE ''%'' + @PayResponsein + ''%'')'

If @PayRefNoin is Not Null
Set @SQLQuery = @SQLQuery + ' And (PayRefNo LIKE ''%'' + @PayRefNoin + ''%'')'

If @PayBankCodein is Not Null
Set @SQLQuery = @SQLQuery + ' And (PayBankCode LIKE ''%'' + @PayAccountin + ''%'')'

If @PayDatetimein is Not Null
Set @SQLQuery = @SQLQuery + ' And (cast(PayDatetime as date) = ( select convert(date , @PayDatetimein , 103)))'

Set @ParamDefinition =
'@PayAccountin as varchar(10),
@PayCustNamein as varchar(30),
@PayAmountin as int,
@PayAmountPaidin as int,
@PayResponsein as char (2),
@PayRefNoin as varchar (120),
@PayBankCodein as varchar (6),
@PayDatetimein as varchar(50)'


Execute sp_Executesql
@SQLQuery,
@ParamDefinition,
@PayAccountin,
@PayCustNamein,
@PayAmountin,
@PayAmountPaidin,
@PayResponsein,
@PayRefNoin,
@PayBankCodein,
@PayDatetimein



If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)

ErrorHandler:
Return(@@ERROR)


Right now i use the stored procedure on vb.net code behind and get the data as datatable and process on VB.net Code behind.

but now I Want to get the table result query and progress to the same
stored procedure
to change some of the field value, is it possible to catch the table first on the stored procedure, change the value and give output to be used on vb.net ?

thanks for your help.

Answer

Try this one -

ALTER PROCEDURE [dbo].[SearchPayment]

    @PayAccountin AS VARCHAR(10),
    @PayCustNamein AS VARCHAR(30),
    @PayAmountin AS INT,
    @PayAmountPaidin AS INT,
    @PayResponsein AS CHAR (2),
    @PayRefNoin AS VARCHAR (120),
    @PayScreenTextin AS VARCHAR (100),
    @PayReceiptTextin AS VARCHAR (350),
    @PayDatetimein AS VARCHAR(50),
    @PayBankCodein AS VARCHAR (6)

AS BEGIN

    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @OwnTran BIT

    SET @OwnTran = 0

    IF @@TRANCOUNT = 0 
        BEGIN 
            SET @OwnTran = 1
            BEGIN TRAN
        END

    BEGIN TRY

        DECLARE @SQLQuery NVARCHAR(4000)

        SELECT @SQLQuery = '

        IF OBJECT_ID (N''tempdb.dbo.##test'') IS NOT NULL
        DROP TABLE ##test

        SELECT * 
        INTO ##test
        FROM payment 
        WHERE PayId is not null  ' 
                + ISNULL(' And (PayAccount LIKE ''%''' + @PayAccountin + '''%'')', '')
                + ISNULL(' And (PayCustName LIKE ''%''' + @PayCustNamein + '''%'')', '') 
                + ISNULL(' And (PayAmount LIKE =' + CAST(@PayAmountin AS VARCHAR(5)) + ')', '')
                + ISNULL(' And (PayAmountPaid =' + CAST(@PayAmountPaidin AS VARCHAR(5)) + ')', '')
                + ISNULL(' And (PayResponse LIKE ''%''' + @PayResponsein + '''%'')', '')
                + ISNULL(' And (PayRefNo LIKE ''%''' + @PayRefNoin + '''%'')', '')
                + ISNULL(' And (PayBankCode LIKE ''%''' + @PayAccountin + '''%'')', '')
                + ISNULL(' And (cast(PayDatetime as date) = convert(date, @PayDatetimein , 103))', '')

        EXEC sys.sp_Executesql @SQLQuery

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0 
            ROLLBACK TRAN

    END CATCH

    IF @OwnTran = 1
        AND @@TRANCOUNT > 0 
        BEGIN
            COMMIT TRAN
        END
END