Filtering output - 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

Filtering output

Author

Topic

jciarlette
Starting Member

6 Posts

Posted - 2008-11-19 : 07:34:38

I am trying to filter output by having user select column to search in for text the user inputs into a text field.
Here is my query statement:
SELECT [ClientID], [DonorID], [DonorSSN], [ResultID], DateReported],
[DateReceived], [DateCollected], [SpecimenID]
FROM [tstmohmsResults].[dbo].[LabResults]
WHERE ((select [SqlViewColumn]
from [tstmohmsResults].[dbo].[GridHeaders]
where [GridHeader] = 'Donor') like '%ch%')
AND ([ClientChar] = (Select [ClientID]
FROM [tstmohmsResults].[dbo].[UserNames]
WHERE ([LoginID] = 'john'))
OR (Select [ClientID]
FROM [tstmohmsResults].[dbo].[UserNames]
WHERE ([LoginID] = 'john')) = '-1')

The text in purple is the column/categroy the user selects from a dropdown list which is then used to query the table that contains the data I want the user to see and the text in blue is the filtering text. When the search text (blue) is '%' I see all the records which is what I want. When the search text (blue) is something other than '%', no records are shown.
I believe it has something to do with the column/category that I am trying to filter in. It is like the sql query is not looking at the variable, which is in red, as a table column. What am I doing wrong or is there a better way of doing this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:39:16

What's up with DateReported column name?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:44:59

[code]SELECT lr.ClientID,
lr.DonorID,
lr.DonorSSN,
lr.ResultID,
lr.DateReported,
lr.DateReceived,
lr.DateCollected,
lr.SpecimenID
FROM tstmohmsResults.dbo.LabResults AS lr
INNER JOIN tstmohmsResults.dbo.UserNames AS un ON un.LoginID = 'john'
LEFT JOIN tstmohmsResults.dbo.GridHeaders AS gh ON gh.GridHeader = @Param1
AND gh.SqlViewColumn LIKE @Param2 + '%'
AND gh.ClientChar = un.ClientID
AND un.ClientID = '-1'
WHERE un.ClientID = '-1'
OR gh.gridheader is not null[/code]

E 12°55'05.63"
N 56°04'39.26"

jciarlette
Starting Member

6 Posts

Posted - 2008-11-19 : 23:46:13

Your script looks great. I am trying it now. I will let you know how it goes. Thank you for responding. As you can probably see I am new to SQL scripting.

jciarlette
Starting Member

6 Posts

Posted - 2008-11-30 : 17:58:32

I have changed the query a little. Currently I am testing this in SQL Query studio express:

SET @FilterCat = 'tlr.DonorName'
SET @FilterTxt = 'm'
SET @UserName = 'john'
SET @Cl_ID =
(SELECT MIN(tUR.clientID)
FROM tstmohmsResults.dbo.UserRights AS tUR
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID
WHERE tU.UserName = @UserName)

IF ( @Cl_ID <> -1 )
BEGIN
SELECT tlr.ClientName,
tlr.DonorName,
tlr.DonorSSN,
tlr.ResultName,
tlr.DateReported,
tlr.DateReceived,
tlr.DateCollected,
tlr.SpecimenID
FROM tstmohmsResults.dbo.LabResults AS tLR
INNER JOIN tstmohmsResults.dbo.UserRights AS tUR ON tlr.clientID = tUR.clientID
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID
WHERE (tU.UserName = @UserName AND @FilterCat Like ('%' + @FilterTxt + '%'))
END
ELSE
BEGIN
SELECT tlr.ClientName,
tlr.DonorName,
tlr.DonorSSN,
tlr.ResultName,
tlr.DateReported,
tlr.DateReceived,
tlr.DateCollected,
tlr.SpecimenID
FROM tstmohmsResults.dbo.LabResults AS tLR
WHERE @FilterCat Like @FilterTxt
END

@FilterCat will be selected from a Dropdown list and @FilterTxt will be from a text box in asp. Every time I run the query it returns all entries for user 'john' no matter what @FilterTxt is set to. If I set @FilterTxt = 'm' I expect tlr.DonorName which has the letter 'm' in them to be returned. Instead all records for user 'john' are returned. If I modify 'AND @FilterCat Like ('%' + @FilterTxt + '%')' to 'AND tlr.DonorName LIKE '%m%')' then it returns only the record sets whose DonorName contains the letter 'm'. What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 23:29:49

you cant pass string for column name like this. for thst you need dynamic sql

DECLARE @Sql varchar(8000)
SET @FilterCat = 'tlr.DonorName'
SET @FilterTxt = 'm'
SET @UserName = 'john'
SET @Cl_ID = 
(SELECT MIN(tUR.clientID) 
FROM tstmohmsResults.dbo.UserRights AS tUR 
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID 
WHERE tU.UserName = @UserName) 

IF ( @Cl_ID <> -1 ) 
BEGIN 
SET @sql='SELECT tlr.ClientName, 
tlr.DonorName, 
tlr.DonorSSN, 
tlr.ResultName, 
tlr.DateReported, 
tlr.DateReceived, 
tlr.DateCollected, 
tlr.SpecimenID 
FROM tstmohmsResults.dbo.LabResults AS tLR 
INNER JOIN tstmohmsResults.dbo.UserRights AS tUR ON tlr.clientID = tUR.clientID 
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID 
WHERE (tU.UserName = @UserName  AND '+@FilterCat+' Like (''%'' + @FilterTxt + ''%''))'
EXEC (@Sql)
END 
ELSE 
BEGIN 
SET @Sql='SELECT tlr.ClientName, 
tlr.DonorName, 
tlr.DonorSSN, 
tlr.ResultName, 
tlr.DateReported, 
tlr.DateReceived, 
tlr.DateCollected, 
tlr.SpecimenID 
FROM tstmohmsResults.dbo.LabResults AS tLR 
WHERE '+@FilterCat + ' Like (''%'' + @FilterTxt + ''%'')'
EXEC (@Sql)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 23:30:26

But can you explain why you want to send column to be searched through a parameter?

jciarlette
Starting Member

6 Posts

Posted - 2008-11-30 : 23:58:23

I want the user to select, from a dropdown list, which column to filter against, then type the text they want to filter on. for example:

LName FName Rank Serial#
Keith Toby Seargent 40276
Skinner Doug Corporal 38765
Tripp Richard Private 12345

DropDownList (User would select any column heading) SearchTextBox (User would type in some letters)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 00:03:28

Ok..But if number of items in column list combo is small try using CASE WHEN instead of dynamic sql.

jciarlette
Starting Member

6 Posts

Posted - 2008-12-02 : 01:53:58

With the dynamic sql as above I get this error message:
Must declare the scalar variable "@UserName".

I have tried:
EXEC sp_execsql @Sql, N'@UserName nchar(10)','@FilterCat nvarchar(255)'

but get same error. I am trying to figure it out. What else do I have to do?

This is the full statement:

Declare @Sql nvarchar(MAX)
Declare @Cl_ID int
Declare @UserName nchar(10)
Declare @FilterCat nvarchar(255)
Declare @FilterTxt nvarchar(255)

SET @FilterCat = 'tlr.DonorName'
SET @FilterTxt = 'm'
SET @UserName = 'john'
SET @Cl_ID =
(SELECT MAX(tUR.clientID)
FROM tstmohmsResults.dbo.UserRights AS tUR
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID
WHERE tU.UserName = @UserName )

IF ( @Cl_ID <> -1 )
BEGIN
SET @sql='SELECT tlr.ClientName,
tlr.DonorName,
tlr.DonorSSN,
tlr.ResultName,
tlr.DateReported,
tlr.DateReceived,
tlr.DateCollected,
tlr.SpecimenID
FROM tstmohmsResults.dbo.LabResults AS tLR
INNER JOIN tstmohmsResults.dbo.UserRights AS tUR ON tlr.clientID = tUR.clientID
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID
WHERE (tU.UserName = @UserName AND '+@FilterCat+' Like (''%'' + @FilterTxt + ''%''))'
EXEC (@Sql)
END
ELSE
BEGIN
SET @Sql='SELECT tlr.ClientName,
tlr.DonorName,
tlr.DonorSSN,
tlr.ResultName,
tlr.DateReported,
tlr.DateReceived,
tlr.DateCollected,
tlr.SpecimenID
FROM tstmohmsResults.dbo.LabResults AS tLR
WHERE ' + @FilterCat + ' Like (''%'' + @FilterTxt + ''%'')'
EXEC sp_executesql @Sql, N'@UserName nchar(10)','@FilterCat nvarchar(255)'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 02:06:50

should be something like

DECLARE @Sql nvarchar(4000),@ParamList nvarchar(4000)
SET @ParamList=N'@UserName nchar(10),@FilterCat nvarchar(255),@FilterTxt nchar(1)'
SET @FilterCat = N'tlr.DonorName'
SET @FilterTxt = N'm'
SET @UserName = N'john'
SET @Cl_ID = 
(SELECT MIN(tUR.clientID) 
FROM tstmohmsResults.dbo.UserRights AS tUR 
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID 
WHERE tU.UserName = @UserName) 

IF ( @Cl_ID <> -1 ) 
BEGIN 
SET @sql='SELECT tlr.ClientName, 
tlr.DonorName, 
tlr.DonorSSN, 
tlr.ResultName, 
tlr.DateReported, 
tlr.DateReceived, 
tlr.DateCollected, 
tlr.SpecimenID 
FROM tstmohmsResults.dbo.LabResults AS tLR 
INNER JOIN tstmohmsResults.dbo.UserRights AS tUR ON tlr.clientID = tUR.clientID 
INNER JOIN tstmohmsResults.dbo.Users AS tU ON tUR.UserID = tU.UserID 
WHERE (tU.UserName = @UserName  AND '+@FilterCat+' Like (''%'' + @FilterTxt + ''%''))'
EXEC sp_executesql @sql,
     @ParamList,
     @UserName,@FilterCat,@FilterTxt

END 
ELSE 
BEGIN 
SET @Sql='SELECT tlr.ClientName, 
tlr.DonorName, 
tlr.DonorSSN, 
tlr.ResultName, 
tlr.DateReported, 
tlr.DateReceived, 
tlr.DateCollected, 
tlr.SpecimenID 
FROM tstmohmsResults.dbo.LabResults AS tLR 
WHERE '+@FilterCat + ' Like (''%'' + @FilterTxt + ''%'')'
EXEC sp_executesql @sql,
     @ParamList,
     @UserName,@FilterCat,@FilterTxt
END

jciarlette
Starting Member

6 Posts

Posted - 2008-12-02 : 02:17:59

Thank you very much for sticking with me, visakh16and Peso. It works now. I did have to declare all the @ variables for it to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 02:34:58

Yup. you need to
You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources