sql

gistfile1.sql

ALTER PROCEDURE [dbo].getNpiD
	@lname nvarchar(40) = null,
	@fname nvarchar(40) = null,
  @npi bigint = null,
  @State nvarchar(40) = null
AS
DECLARE @sql        nvarchar(MAX),
@paramlist  nvarchar(4000)

SELECT @sql = 'SELECT * FROM NPI3 npi
WHERE 1=1'

IF @lname IS NOT NULL                                          
   SELECT @sql += ' AND npi.lname = @lname'               
     
IF @fname IS NOT NULL                                          
   SELECT @sql += ' AND npi.fname = @fname'  

IF @npi IS NOT NULL                                          
   SELECT @sql += ' AND npi.npi = @npi'  

IF @State IS NOT NULL                                          
   SELECT @sql += ' AND npi.State = @State'  


SELECT @paramlist = '@lname nvarchar(40),
	@fname nvarchar(40),
  @npi bigint,
  @State nvarchar(40)'

EXEC sp_executesql @sql, @paramlist, @lname, @fname, @npi, @State

gistfile2.sql

ALTER PROCEDURE [dbo].getNpi3
	@lname nvarchar(40) = null,
	@fname nvarchar(40) = null,
 @npi bigint = null,
 @State nvarchar(40) = null
 
AS
	SELECT * FROM NPI3 as npi
 WHERE (npi.lname = @lname OR @lname IS NULL)
 AND (npi.fname = @fname OR @fname IS NULL)
 AND (npi.npi = @npi OR @npi IS NULL)
 AND (npi.State = @State OR @State IS NULL)

OPTION (RECOMPILE)