Convert Dynamic Stored Proc

Question

I was wondering if there was a way to make this dynamic sp to a non-dynamic 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

Thanks for any help

Answer 1

YOu can convert it but you would have code that is less efficient than what you have. Why would you do that? This type of search proc is one of the few places where dynamic SQL is the best method.