捕获sp_executesql表结果

Question

我已经使用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)

现在我使用vb.net代码上的存储过程，并将数据作为数据表并在vb.net代码上处理。

但现在我想获得表结果查询和进度到相同的stored procedure以改变一些字段值，是否有可能在存储过程中首先捕获表，更改值并给出要在vb.net上使用的输出?

谢谢你的帮助。

Answer 1

试试这个-

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