InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 11:43:23 GMT20http://www.sqlservercentral.com/Forums/Topic788603-338-1.aspx[quote]I am unsure what the SELECT -1 does [/quote]This alows you to display a simple, maybe default, empty selection in your combo, maybe to allow for "every option" or "ignore this filter", as I'll show you here:[code="sql"]SELECT po.PurchaseOrderId,p.PrefixIdFROM PurchaseOrders AS poINNER JOIN Prefix AS p ON po.Prefix = p.prefixWHERE p.PrefixId = @PrefixOR @Prefix = -1[/code]This OR @Prefix = -1 allows you to pass -1 and actually ignore the filter to return all rows.Tue, 15 Sep 2009 20:42:09 GMTdbo.benyoshttp://www.sqlservercentral.com/Forums/Topic788603-338-1.aspxHi. I have developed quite a few reports but have not yet mastered allowing a user to SELECT 1 or multiple values from a report parameter. I have used:SELECT -1 Prefix,'<All Prefixes>' AS PrefixIdUNIONSELECT DISTINCT Prefix,PrefixIdFROM PrefixThis allowed me to return all values for prefixes then filter on a single prefix. I adapted the above from something I found in another report. To be honest I am unsure what the SELECT -1 does so feel free to educate me!I have the main dataset query.....something like:SELECT po.PurchaseOrderId,p.PrefixIdFROM PurchaseOrders AS poINNER JOIN Prefix AS p ON po.Prefix = p.prefixWHERE p.PrefixId = @PrefixI would then create a second dataset for the lookup values for the prefixes:SELECT p.PrefixIdFROM PrefixesI would then set the @Prefix parameter from Query. This allows for a single value to be select.I have read a little on COALESCE + Cursors & temp tables + UDF. I have only worked with temp tables & CTE's.Can anyone offer me some advice on how to piece it all together?Many Thanks,Phil.Tue, 15 Sep 2009 16:10:12 GMT2Tall