Dynamically add the checkboxlist checked value of in a stored procedure of sql server 2008

Posted on 2012-03-24
Last Modified: 2012-08-13

I am using 3 tiers Architecture, i have 3 checkboxlists and there are several checkboxes in each checkboxlist, im sending the parameters of every checked checkbox value to sqlserver 2008 as a parameter and then include it in the select statement.

the problem i have is im sending all checkboxes as a parameter and then in sql server stored procedure im checking with if statement that if this is not empty then include it in the select statement so for this i have included a lot of if statements,

my question is that is there any way to build a dynamically sql server stored procdeure which check the parameters that if it is not empty then it include it in the select statement otherwise ignore it.

any help will be appreciated.

Question by:shah36
  • 3
  • 2
LVL 13

Expert Comment

ID: 37761346
Would not it be better to build field names separated by comma and pass it to the stored procedure?

For example, if checkbox1 is checked, checkbox2 is not checked, checkbox3 is checked and checkbox4 is checked, then

you pass "field1, field3, field4" to the stored procedure.  By the way the string you pass would become fully dynamic.

then in your stored procedure, the select statement would look something like.....
select field1, field3, field4 from mytable

Just an idea.


Author Comment

ID: 37761352
thanks ashok, please can u give me an example as  i need to write the stored procedure in sql server , how can i make the string in and then how do i convert that string in stored procedure and use it in the select statement in sql server.

LVL 13

Assisted Solution

Ashok earned 500 total points
ID: 37761429
Create stored procedure like this.....

USE [AdventureWorks]

ALTER PROCEDURE [dbo].[TestFieldPass]
  @field_names varchar(500)
  set nocount on
  set ansi_warnings off
  declare @strSQL varchar(1500);
  set @strSQL = 'select ' + @field_names + ' from Person.Contact';


USE [AdventureWorks]

DECLARE      @return_value int

EXEC      @return_value = [dbo].[TestFieldPass]
            @field_names = N'Phone,EmailAddress'

SELECT      'Return Value' = @return_value

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

LVL 13

Accepted Solution

Ashok earned 500 total points
ID: 37761468
On ASP.NET side (assuming CheckBoxList1.Items[i].Text has fieldname)

string sql ='';

      for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        if (CheckBoxList1.Items[i].Selected == true)
         sql += CheckBoxList1.Items[i].Text + ",";

sql.Remove(sql.Length - 1, 1);

Open in new window

Now you can pass sql (as FieldNames parameter to your Stored Procedure).

LVL 75

Expert Comment

by:Anthony Perkins
ID: 37763798
Please post your Stored Procedure as it stands and we can make more appropriate suggestions.  Typically you can pass all your checkbox values as a single parameter, either as a tinyint or Xml.

Author Closing Comment

ID: 37860140
Thank you very much for your help. It worked really fine.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Compare data between two databases 16 103
Help with SQL joins 9 48
How to place a condition in a filter criteria in t-sql? 12 68
SQL query to summarize items per month 5 59
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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