Example of counting records in a table based upon filter

Posted on 2012-08-22
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 65

    Accepted Solution

    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
    @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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Moving SQl Server SBS 2003 to SQL Server 2014 27 97
    SQL Searching 3 34
    sql sproc 2 21
    Delete from table 6 26
    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now