Example of counting records in a table based upon filter

Posted on 2012-08-22
Medium Priority
Last Modified: 2012-08-22

I created a SQL script that takes parameters to return counts.  There are three possible parameters: Filter Type, Project ID, and Form Name.  Here is the Stored Procedure from SQL Server 2005:

	-- Parameters
	@sFilter	nvarchar(6),
	@iProject	int = NULL,
	@sForm		nvarchar(36) = NULL


		-- Select 'ALL' Record(s) in Table
		IF @sFilter = 'ALL'
			FROM	dbo.tblDataGridViewDesigns
		-- Select 'ALL' Record(s) by Project in Table
		IF @sFilter = 'PROJECT'
			FROM	dbo.tblDataGridViewDesigns
			WHERE	(
					Design_LinkProject	= @iProject
		-- Select 'ALL' Record(s) by Form in Table
		IF @sFilter = 'FORM'
			SELECT	COUNT(Design_Name) AS Expr1
			FROM	dbo.tblDataGridViewDesigns
			WHERE	(
					(Design_LinkProject	= @iProject)
			AND		(Design_FormName	= @sForm)

Open in new window

Right now I want to filter of a particular Project ID on 1 and supplied the parameters: @sFilter = "PROJECT", @iProject = 1, @sForm = NULL.

The returned value is 0.  I have three records in the table.  The field, "Design_LinkProject" shows 1 for all three so I should get a value of 3 returned.

Any ideas please?  Thanks!
Question by:Peter Allen
LVL 66

Accepted Solution

Jim Horn earned 600 total points
ID: 38320685
Your script declares @sFilter as a variable, but it never assigns a value to it, so it is NULL when it hits your BEGIN ... END block.

Also, @sFilter      nvarchar(6) is one character shy of containing the seven-character 'PROJECT' value, so you'll get a truncation message, it'll store 'PROJEC', and not hit anything in your IF blocks.

Author Closing Comment

by:Peter Allen
ID: 38321612
@sFilter is supplied as a parameter, but what I realized after reading your message is that I forgot to increase the nvarchar value from 6 to 7.  Now the code works properly.  Thank you.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question