CODEWITHSUNDEEP
sqlselectstored-procedures
Alaa
Alaa

Reputation: 217

Filter table data using select sql statement

I want to filter table data using select statement, I have four columns, and I have also four text boxes to enable search in each column,and I can enter value in any box(es), when I enter value in text box(es) I want to return the record(s) that match the value(s) I have entered, how can I do that?

ALTER PROCEDURE dbo.test_search

    (
        @ID int,
    @FirstName nvarchar(50),
    @MiddleName nvarchar(50),
    @LastName nvarchar(50)
    )

AS
    SELECT     ID, FirstName, MiddleName, LastName
    FROM         StudentsInformation
    WHERE (@ID IS NULL OR StudentsInformation.ID = @ID) AND
           (@FirstName IS NULL OR StudentsInformation.FirstName = @FirstName )AND
           (@MiddleName IS NULL OR StudentsInformation.MiddleName = @MiddleName )AND
           (@LastName IS NULL OR StudentsInformation.LastName = @LastName )
    RETURN

Upvotes: 3

Views: 14256

Answers (4)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Firstly you need to find text-box in which search string is passed.

Depending on text-box the query could be written on the related the column.

select * from table_name where column like '%text-box value%'

Edit

SELECT ID,FirstName,MiddleName,LastName
FROM  StudentsInformation
WHERE 1=1
ID=(case when @ID <>0 AND @ID IS NOT NULL then @ID else ID end)
and FirstName=(case when @FirstName<>'' and @FirstName IS NULL then @FirstName 
else FirstName)
and MiddleName=(case when @MiddleName<>'' and @MiddleName IS NULL then @MiddleName 
else MiddleName)
and LastName=(case when @LastName<>'' and @LastName IS NULL then @LastName 
else LastName)

Upvotes: 0

kyooryu
kyooryu

Reputation: 1509

EDIT: 

SELECT 
id
, firstname
, middlename
, lastname    
FROM studentsinformation
WHERE id = @id        
OR firstname LIKE '%' + @firstname + '%'
    OR middlename LIKE '%' + @middlename + '%'
    OR lastname LIKE '%' + @lastname + '%'

You can swap OR for AND if you want to select records that are true for all the checkboxes.

Upvotes: 2

Nishant Jain
Nishant Jain

Reputation: 19

I hope this will work:

Select * from <table-name> 
where 
<Column-name1> Like 'TextBox1%'
or
<Column-name2> Like 'TextBox2%'
or
<Column-name3> Like 'TextBox2%'
or
<Column-name4> Like 'TextBox4%'

Upvotes: 0

Freelancer
Freelancer

Reputation: 9064

Syntax Depends upon programming language which you use.

But generally:

string sql="select * from tableName where"

if((txt1.Text!="")&&(sql=="select * from tableName where")
sql=sql+"colName like % @txt1 %"
else
sql=sql+" and colName like % @txt1 %"



if((txt2.Text!="")&&(sql=="select * from tableName where")
sql=sql+"colName like % @txt2 %"
else
sql=sql+" and colName like % @txt2 %"


if((txt3.Text!="")&&(sql=="select * from tableName where")
sql=sql+"colName like % @txt3 %"
else
sql=sql+" and colName like % @txt3 %"

Do like this.

Upvotes: 0

Related Questions