How can we create a dynamic sql query to run without considering if the parameters are passed or not in c# windows forms -

i using vs 2012 , sql express

i trying build windows forms application search through database in c# , has different controls on form passed parameters query.

the parameters in query not passed times

i trying following code sample.

select a.id 'dealid', a.tradedate, c.companyname 'seller company',         a.sellcommission, h.broker_fullname 'seller trader',         j.displayname 'seller broker', d.companyname 'buyer company',         a.buycommission, g.broker_fullname 'buyer trader',         i.displayname 'buyer broker', e.product_name, f.type_desc 'quantity type',        f.nbr_of_gallons 'quantity multiplier', a.contractvolume, a.totalvolume,         a.deliverypoint, a.price, a.contractstart, a.contractend confirmations (nolock)  left outer join company c (nolock)    on c.company_id = a.sellcompany left outer join company d (nolock)    on d.company_id = a.buycompany  left outer join bioproducttypes e (nolock)    on e.id = a.productid  left outer join bio_quantity_type f (nolock)    on f.id = a.quantitytypeid  left outer join companybroker g (nolock)    on g.companybroker_id = a.buytrader  left outer join companybroker h (nolock)    on h.companybroker_id = a.selltrader  left outer join users (nolock)    on i.id = a.buybroker  left outer join users j (nolock)    on j.id = a.sellbroker (@fromdate null or @fromdate=' ' or a.tradedate >= @fromdate)         , (@todate null or @todate=' ' or a.tradedate <= @todate)         , (@buycompanyname null              or @buycompanyname=""              or a.buycompany = (select company_id                                company                                (companyname = @buycompanyname)))                      , (@sellcompanyname null              or @sellcompanyname=""              or a.sellcompany = (select company_id                                 company                                 (companyname =@sellcompanyname)))         , (@product null or @product="" or e.product_name= @product)"; 

rather using above query, can dynamically create query, based on parameters passed seems more logical doesn't records if column in table has null value.

if calling stored procedure, i'd suggest dynamically building sql string use parameters you'll using, calling sp_executesql. stored procedure this:

declare @sql =nvarchar(max), @parameters nvarchar(max) set @sql = 'select * [dbo].[foo] column1 = @param1' set @parameters = '@param1 nvarchar(32), @param2 nvarchar(32)' if(@param2 not null , @param2 <> ' ') set @sql = @sql + ' , column2 = @param2' exec sp_executesql @sql, @parameters, @param1, @param2 

the idea same if you're building query string in c# instead of stored procedure:

command.commandtext = "select * [dbo].[foo] column1 = @param1"; command.parameters.addwithvalue("@param1", param1); if(!string.isnullorempty(param2)) {     command.commandtext += " , column2 = @param2";     command.parameters.addwithvalue("@param2", param2); }