Dynamic Sql using Checkboxes!!! Tricky!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Sql using Checkboxes!!! Tricky!

Author

Topic

sydtek22
Starting Member

10 Posts

Posted - 2008-03-26 : 16:28:56

I have a page that is full of checkboxes, I have tried at least twenty different ways of getting this to work, but to no avail. I want when someone checks a box, that the inputs that were checked to be included in the SELECT statement in the SQL. It's kinda like unknown variables in the statement. What I want is SELECT "checkbox values" FROM TableData. How do I get the user to be able to choose the columns they want pulled from the database. Its all in one table. Here is what i have.

<cfinput type="checkbox" name="Selected" value="firstname">
<cfinput type="checkbox" name="Selected" value="lastname">
<cfinput type="checkbox" name="Selected" value="email">

Can anyone point me in the right direction of the sql syntax or procedures which I'm not good at, to use to accomplish this?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 16:33:28

Call an sp passing the values selected or null for the parameters and in the query

where (firstname = @firstname or @firstname is null)
where (lastname = @lastname or @lastname is null)
...

You might find performance problems with that but once it's in the sp it's easy to change to optimise.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sydtek22
Starting Member

10 Posts

Posted - 2008-03-26 : 22:44:40

I thought about stored procedures but thats still looking for the selections after the SELECT statement. I've tried using cfifs and anything that seems logical but nothing. I was digging dynamic SQL and retrieving unknown variables from the database. Something about a Method 4. All I know is that i don't understand yet. But wouldn't the method you just explained pull all the columns or just what shows up as Yes or True from the checkboxes.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 22:50:15

In the app you only populate the parameters that are selected - leave the rest null and they will not affect the query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-27 : 00:25:50

DECLARE @SelectClause varchar(2100)
DECLARE @SelectList varchar(2100)
DECLARE @FirstSelect INT

SET @SelectClause='SELECT'
SET @FirstSelect=0

IF(FirstName.status=SELECTED) -- I dont know exact syntax for IF logic, but it shoud just check if the checkbox is selected
BEGIN
IF @FirstSelect=0
BEGIN
SET @SelectList=FirstName -- "FirstName" is the column name
SET @FirstSelect=1
END
ELSE
BEGIN
SET @SelectList=@SelectList+','+FirstName
END
END

--The above IF should be repeated for all the columns(check boxes)
.
.
.
.

SET @SelectClause=@SelectClause+' '+@SelectList+' '+'FROM TABLENAME'

EXEC(@SelectClause)

-- While creating the script, you can use PRINT(@SelectClause) instead of EXEC(@SelectClause)to see if you are getting what you want

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources