转换动态存储过程

Question

我想知道是否有办法使这个动态 sp 成为非动态 sp

CREATE PROCEDURE [dbo].[GetPeople]
    (
        @id varchar(8) = NULL,
        @lastName varchar(50) = NULL,
        @firstName varchar(50) = NULL,
        @birthdate date = NULL,
        @ssn varchar(9) = NULL,
        @driversLicense varchar(12) = NULL,
        @gender varchar(1) = NULL,

        @addressNumber varchar(8) = NULL,
        @addressFraction varchar(3) = NULL,
        @addressDirection varchar(3) = NULL,
        @street varchar(45) = NULL,
        @addressSuffix varchar(4) = NULL,
        @addressPostDirection varchar(2) = NULL,
        @addressUnitType varchar(6) = NULL,
        @addressUnit varchar(8) = NULL,
        @city varchar(22) = NULL,
        @zip varchar(5) = NULL,
        @addressStatus varchar(1) = NULL

    )
AS
    SET NOCOUNT ON

    DECLARE @q nvarchar(4000),
            @paramlist  nvarchar(4000)  

    SELECT @q = ' SELECT * FROM People WHERE 1 = 1' 

    IF @certnum <> '' 
        SELECT @q = @q + ' AND ID = ''' + @id  + ''''
    ELSE 
    BEGIN
        IF @lastName  <> ''                                           
            SELECT @q = @q + ' AND lastname like ''' + @lastName + '%'''    
        IF @firstName  <> ''                                           
            SELECT @q = @q + ' AND firstname like ''' + @firstName + '%'''  
        IF @birthdate  is not null                                 
            SELECT @q = @q + ' AND birthdate = ''' + cast(@birthdate as varchar(10)) + ''''
        IF @ssn  <>  ''                                  
            SELECT @q = @q + ' AND ssnum = ''' + @ssn + ''''    
        IF @driversLicense  <>  ''                                  
            SELECT @q = @q + ' AND drivers_license = ''' + @driversLicense + ''''    
        IF @gender  <>  ''                                  
            SELECT @q = @q + ' AND sex = ''' + @gender + '''' 
        IF @street  <> ''                                           
            SELECT @q = @q + ' AND addr_str like ''' + @street + '%'''
        IF @city  <> ''                                           
            SELECT @q = @q + ' AND cityname like ''' + @city + '%'''    
        IF @zip  <> ''                                           
            SELECT @q = @q + ' AND addr_zip like ''' + @zip + '%'''  
        IF @addressNumber  <> ''                                           
            SELECT @q = @q + ' AND ltrim(rtrim(addr_num)) = ''' + @addressNumber + ''''   

    END


    SELECT @q = @q + ' ORDER BY  lastname, firstname'


    SELECT @paramlist = '
        @id varchar(8) = NULL,
        @lastName varchar(50) = NULL,
        @firstName varchar(50) = NULL,
        @birthdate date = NULL,
        @ssn varchar(9) = NULL,
        @driversLicense varchar(12) = NULL,
        @gender varchar(1) = NULL,

        @addressNumber varchar(8) = NULL,
        @addressFraction varchar(3) = NULL,
        @addressDirection varchar(3) = NULL,
        @street varchar(45) = NULL,
        @addressSuffix varchar(4) = NULL,
        @addressPostDirection varchar(2) = NULL,
        @addressUnitType varchar(6) = NULL,
        @addressUnit varchar(8) = NULL,
        @city varchar(22) = NULL,
        @zip varchar(5) = NULL,
        @addressStatus varchar(1) = NULL
'

    PRINT @q
    EXEC sp_executesql @q, @paramlist, 
        @id,

        @lastName,
        @firstName,
        @birthdate,
        @ssn,
        @driversLicense,
        @gender,

        @addressNumber,
        @addressFraction,
        @addressDirection,
        @street,
        @addressSuffix,
        @addressPostDirection,
        @addressUnitType,
        @addressUnit,
        @city,
        @zip,
        @addressStatus

感谢您的帮助

Answer 1

您可以对其进行转换，但您的代码效率会低于您所拥有的代码。你为什么要这么做？这种类型的搜索过程是动态 SQL 是最佳方法的少数几个地方之一。