.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Best way to build a query /sproc to support filtering

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

Hi All,


I have a scenario where I will be getting search results from a front end UI.  If nothing is passed I should return all results, but if the users have selected one to many choices I will need to filter based on those choices.  My delima is how to handle this approach.  The data will be coming in the form of XML and I will need to shred it to get the criteria.

I was thinking I could use "WHERE EXISTS" Clauses and if there are any results passed to me I could fill a table variable with everything and join to it.....if they did make selections then I could fill the table with just those choices. 

I really just don't like the idea of having to fill the table with everythign when nothing is selected. 

Just wondering what others have done to handle this type of scenario?

Thanks !


View Complete Post

More Related Resource Links

SQLXML 3.0: Build Data-Driven Web Services with Updated XML Support for SQL Server 2000


XML is becoming the ubiquitous data format on the Web, and XML support in SQL Server is evolving to meet the additional demand. Using XML, SOAP, HTTP, and SQL Server, you can now build powerful Web Services easily. To show just how simple it is with SQLXML 3.0, this article walks the reader through the process step by step, from setting up a virtual directory enabling data access via HTTP to executing queries and building Web Services. Finally, the author illustrates the creation of two Web Services clients-one with C# that works with the Microsoft .NET Framework and one with the SOAP Toolkit 2.0 for anyone still using earlier development tools.

Christian Thilmany

MSDN Magazine May 2002

does SPD support filtering of the mail title/body we send to a list/library?

for example, if we send a mail with the title of "%hostname% %ipaddress%" to a list. can SPD WF filter the title and get %hostname% to a new variable? the reason to ask this is i want to send a well-formated mail to sharepoint and the automatical WF can recognize the change of the list items and update the relevant columns by the filtered content from mail title or body. thanks!

Trying to build query using DISTINCT or GROUP BY...beginner here

Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

Parse XML To build Dynamic Query

Hi All, I am trying to figure out the best way to handle a scenario where I will be passed XML that will contain criteria for a search. If the user has selected specific filters then those will be sent in the XML and if there is a section that they left unfiltered then it will not be present in the XML (Which would mean everything for that filter should be returned). My question is around the best process to shred the XML and build a dynamic query out of what i am getting out of the XML object. Is there a better way to handle this scenario? Here is my current approach: Shred the XML and put the filtered data into Global Temp Tables so that I can use them to build my dynamic query. Use those temp tables to create "Where Exists" Criteria within the query to filter down the results based on what was passed to me in XML. If one of the search criteria sections wasn't filtered the temp table would have zero rows and I wouldn't add that to the where clause with an exists statement. I used FOR XML PATH('') in the queries to roll up the data into comma separated values. Build the test schema / objects:   -------------------------------------------------------- --Build Test Schema to demonstrate XML Parsing -------------------------------------------------------- SET NOCOUNT ON; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Products]

second filtering in linq query

Hi I need to make another filtering to the query below. I also need to filter out based on this... cc.SubDataList.DataID <> 22 Dim MyQuery = From r In MyList.Where(Function(cc) cc.SubDataList.Count > 0) Select r How can I do that?  

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

How to build long query string



I know that it is basic questions, but I am stuck here.

I have TableOne have fields: USERNAME, PID, GID, TID, SID, NEWSID. I need to write query string to check if those value is already in the table. If not, insert those value to the table.

string AddUserName = UsernameToken(GetUsername);
string InsertString = "insert into TableOne(USERNAME, PID, GID, TID, SID, NEWSID) values (?, ?, ?, ?, ?, ? )" + "  WHERE NOT " + " ( "  [USERNAME] = '" + AddUserName + "' + " PID = '" + DropDownBoxPID.Text.Trim() +
 '" + " AND " + " GID = '" + DropDownBoxGID.Text.Trim()  +
 '"  + " AND " + "  TID = '" + DropDownBoxtid.Text.Trim()  +  
 '" + " AND " + " SID = '" + Textboxsid.Text.Trim() +  
 '" + " AND " + " NEWSID = '" + DropDownssid.Text.Trim() +
 '" + ")" ;

However, I got the many errors:

Error    1    Too many characters in character literal  

Error    3    Newline in constant   

Is any way to debug this? As always, your help is highly a

SSRS - Filtering Query Results



How can I hide NULL rows in my SSRS report? I want to filter this cellset based on my "Sale Unit" measure. If it is null then its row should be completely hidden.

Here is my MDX Query behin the report:

MEMBER Measures.[DC Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[DcInventory] )

MEMBER Measures.[Store Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[StoreInventory] )

SELECT NON EMPTY { [Measures].[Sale Unit],[Measures].[DC Stock Level],[Measures].[Store Stock Level] } ON COLUMNS,






MDX Query Filtering Out Data That I Want


I am still learning MDX and am struggling with a query that is feeding a SSRS report. What I am trying to do is provide a data set that breaks down the measures by Product Family, Machine and then Product Code. This is a parameterized query from SSRS where the three groupings are all multi-select parameters.

I have been experimenting with Non Empty and the NonEmpty function trying to eliminate rows that contain all NULL's for all measures. The query below does accomplish that. However, during my troubleshooting, I noticed one case when I failed to eliminate the null rows. In that case, there was one row that had 2 columns with data. All the rest were NULL. No matter how I formulated the query I could not get that one row to show up with the others AND have the rest of the rows with NO data filtered out.

The query below I captured using the Profiler & Excel and then hacked it to give me what I wanted. The result set includes many columns that are percentages so my desire was to use the Aggregate function in the report for the grouped roll-ups to ensure that I am getting the appropriate aggregates. Speaking of which, how do I have the rollup aggregate values restricted to only the data displayed in the report? In other words, what I am seeing is that the aggregate is for the entire grouping including items filtered out of the report. For example, I see t

DFWP query string parameter breaks column header filtering?



We have created a page (using SPD) which accepts a query string parameter to set the data filter for the DFWP. The data is displayed with a basic table layout and "sorting and filtering on column headers" is enabled. But it appears that only the sorting works. When a user clicks on the drop-down for a column header, "Ascending", "Descending", and "Loading..." is displayed. After a few seconds, "Loading..." changes to "Clear Filter" (disabled) but there is no distinct list of values to select as a filter. If we remove the query string parameter from the underlying DFWP query, it works fine. Has anyone else noticed this behavior and/or have a solution?

Importing a query from an .mdx file - Support (SSRS 2008)


I'm writing an MDX query which would be used as the main dataset. The issue that I'm facing is that the query is really big because of it's nature of members and requirement and Reporting Services doesn't support the number of characters that I'm using. So I saw this Import button and imported the MDX query. However I don't get the list of members in the fileds section which I can use in the Report Textbox. I was reading http://msdn.microsoft.com/en-us/library/dd239327.aspx and it says that "Importing a query from an .mdx file is not supported"....

My question is would it every be supported? Is there a workaround for what I'm trying above? I would appreicate any help on the same.

Thank you!

Writing a keyword query for Filtering the search items


I am trying to search only the documents created by a an author. Below is the code that i am trying to use for keyword query but i am not getting any results at all.

string strQuery = "contentclass:" + "STS_ListItem_DocumentLibrary" + "author:" + strQueryText

Can anyone help me out here please.


Filtering Support Contact Field Based On Three Column Selectors in SPD 2007


How do I get a filtered list of support contacts and pre-fill/update that form field based on the selectors/list columns Application Group, Application, and Database in Sharepoint Designer.  Only application group is required and, depending upon what is selected in application group, it may or may not cascade application column entries to the application selector which, in turn, may or may not cascade database column entries to the database selector. 

So, not all selectors will have a value and I need to filter on one, two, or three column values to get by final list of support contacts from that column.  In InfoPath, I was able to do it using a CAML query in code behind but I'm at a loss on how to do it in SPD 2007.   The reason I'm not still using InfoPath is because the powers that be will not allow any code-behind in the collabrative environment.  Any help would be greatly appreciated.

CAML Query - Wildcards or build dynamically?


From what I have found so far, there is no Wildcard in CAML queries against a SharePoint List.  If there is, please let me know what that is.  My problem is that I have seven search criteria that may or may not have data in them.  If a criteria contain spaces or "ALL", I don't want it to be part of the search.  I need those fields to either be wildcarded or be excluded from the query altogether.

If there is no wildcard, does someone have an example of some looping code that will be the query dynamically based on whether a criteria has spaces (or "All) or has data? 

I also am using a DateTimeControl with DateOnly.  I anticipate that will be throwing a wrench into by search results against the SharePoint list.  In the list, it is a datatype of DateTime but "Date Only" is specified.  Any advice about what to watch out for on date fields would be appreciated as well.

ASP.NET, HTML, JavaScript Snippet Support (VS 2010 and .NET 4.0 Series)

This post covers another useful improvement in VS 2010 - HTML/ASP.NET/JavaScript snippet support. Snippets allow you to be more productive within source view by allowing you to create chunks of code and markup that you can quickly apply and use in your application with a minimum of character typing.

Visual Studio has supported the concept of "snippets" for VB and C# in previous releases - but not for HTML, ASP.NET markup and JavaScript. With VS 2010 we now support snippets for these content types as well.

Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

GridView Filtering In ASP.NET

In this tutorial you will learn how to add a filter to a Grid View Control, To modify the query with a parameterized filter, the WHERE Clause, Parameter properties, Parameter Value Editor and To test filtering.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend