How to achieve Dynamic SQL Query with Multiple parmeters to handle null values also My code logic is given below, when one of my input parameter passed as null, the parameterlist should be managed according, how could i proceed for this.

DECLARE @SQL NVARCHAR(MAX)
DECLARE @paramlist NVARCHAR(MAX)


SET @SQL='select [Company ID],
[QSS Company ID],
[Company Name],
[Inactive]  from Company WHERE 1=1'

IF @CompanyID IS NOT NULL
SELECT @SQL = @SQL + ' AND ([Company ID] = @CompanyID)'
Select @paramlist=@paramlist+'@CompanyID BIGINT'
IF @QSSCompanyID IS NOT NULL
SELECT @SQL = @SQL + ' AND ([QSS Company ID] = @QSSCompanyID)'
IF @CompanyName IS NOT NULL
SELECT @SQL = @SQL + ' AND ([QSS Company ID] = @CompanyName)'


EXEC sp_executesql @SQL, N'@CompanyID BIGINT, @QSSCompanyID BIGINT, 
@CompanyName  nvarchar',  @CompanyID,@QSSCompanyID,@CompanyName
There is no harm in having unused parameters passed into sp_executesql.

You approach above is correct.  Declare and pass in all possible parameters for sp_executesql.  You will still get all the benefits of dynamic sql even with unused parameters.