Example of counting records in a table based upon filter

Experts,

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
)

AS

	SET NOCOUNT ON;
	BEGIN
		-- Select 'ALL' Record(s) in Table
		IF @sFilter = 'ALL'
			SELECT	COUNT(*)
			FROM	dbo.tblDataGridViewDesigns
		-- Select 'ALL' Record(s) by Project in Table
		IF @sFilter = 'PROJECT'
			SELECT	COUNT(*) AS Expr1
			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)
					);
	END;

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!
Peter AllenAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Peter AllenAuthor Commented:
@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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.