Dynamic Search - SQL Server Forums

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 Search

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-03-23 : 07:16:00

I have a web based questionaire.The questions are held in a table and the pages are built dynamically on load. There are about 40 questions, some checkboxes and some text. I want to display a copy of the questionaire as a search page so the user can check a box alongside a question he wants includeed in his his search. ie 'has a car Yes', 'lives in Anytown', ''Own House'. In theory you could have any combination of the questions and pass these to a stored procedure to list all persons matching the criteria. Short of building a dynamic sql query to do this has anyone any other ideas on the easiest way to do it ?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-23 : 12:12:23

Each question is numbered. Have question table with the QuNumber (INT) and a QuDescription. Search page builds a string for each question selected e.g. strQuestion = "1,2,3,9,10,22"
Table with answers could contain a question number column which can be used relate to the question table.
So a "Where QuNumber IN (@strNo)" would retrieve answers for particular questions

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-03-24 : 05:18:05

Hi - yes I have this relationship between the tables. My problem is building the string "1,2,3,9,10,22"

Here's my code -how do I strip the last comma ? I get '1,2,3,9,10,22,' - Just one ' not " and also a comma at the end.

Dim strParam As String = "'"

'For Each row As GridViewRow In GVQuestions.Rows
Dim dr As GridViewRow

For Each dr In GVQuestions.Rows
Dim RowCheckBox As System.Web.UI.WebControls.CheckBox = dr.FindControl("ChkBoxFilter")
Dim QId As System.Web.UI.WebControls.Label = dr.FindControl("lblQId")
If RowCheckBox.Checked = True Then
strParam = strParam & QId.Text & ","
End If
Next
strParam = strParam & "'"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-24 : 05:22:00

Dim strParam As String = "'"

'For Each row As GridViewRow In GVQuestions.Rows
Dim dr As GridViewRow

For Each dr In GVQuestions.Rows
Dim RowCheckBox As System.Web.UI.WebControls.CheckBox = dr.FindControl("ChkBoxFilter")
Dim QId As System.Web.UI.WebControls.Label = dr.FindControl("lblQId")
If RowCheckBox.Checked = True Then
strParam = strParam & QId.Text & ","
End If
Next
strParam = strParam & "'"
' trim trailing comma
If Right(strParam,1)="," then
strParam = Left(strParam,len(strParam)-1)
End If

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-03-24 : 05:42:22

Don't I need it in this format to pass to my sp - ie ' around each number ?

WHERE (ANS_QId IN ('202', '194'))

I changed this bit in your code too

' trim trailing comma
If Right(strParam,1)="," then
strParam = Left(strParam,len(strParam)-1)
strParam = strParam & "'"
End If

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-24 : 06:56:24

Sorry about the removing comma after you add a speech mark to string.

Re: speech marks around each number: You can use the Split function below:

CREATE FUNCTION [dbo].[Split] ( @ItemList NVARCHAR(4000), @delimiter CHAR(1) )
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ', ', ',')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item)
VALUES(@Item)
IF @i = 0 SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

---------------------
So the query would be something like:
declare @S varchar(1000)
set @S='1,3,5'

Select * from A
where ID in
(SELECT Item FROM split(@S, ','))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-25 : 03:36:37

Also search for Array+sql server in google

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources