query not returning right results

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

query not returning right results

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-09 : 16:05:51

Hi All,

I have this table that has four columns




A       B       C       D       E
q      TBD     TBD     ABC     NL
r      TBD1    TBD2    DEF     PA
s      TBD3    TBD4    PQR     NL

I have a query like this

SELECT

A, B, C

FROM test1

WHERE

[A] LIKE '' + '%'
OR
[B] >= ''
OR
[c] <= ''
OR
[D] like '' + '%'
OR
[E] like 'NL' = '%'

I want only the rows that has "NL" to return, but I am getting all the rows, rows that has PA in it too are returned. In my form there are four text boxes. If the user enters one of the text box and others are empty then I want to run the query that takes only one parameter. If users selects two text boxes then two parameters should be taken by the stored procedure

create procedure [dbo].[TestData]
(

@A varchar(50), -- admendment
@B varchar(50), -- Begin Year and end year
@C varchar(50),
@D varchar(50),
@E varchar(50) -- cost type

)

AS

IF @A = '' and @B ='' and @c='' and @D='' and @E =''
BEGIN
SELECT

A, B, C

FROM testDATA

END
ELSE
BEGIN

SELECT

A, B, C

FROM testDATA

WHERE

[A] LIKE @A + '%'
OR
[B] like @B + '%'
OR
[c] like @C + '%'
OR
[D] like @D + '%'
OR
[E] like @E = '%'
END

I am passing
EXEC TestData '','','','', 'NL'

How can I modify the query that it returns only rows that has NL in it.

any help will be appreciated.

kostya1122
Starting Member

15 Posts

Posted - 2014-04-09 : 19:55:38

you could try this instead

SELECT

A, B, C

FROM testDATA

WHERE

([A] LIKE @A + '%' or @A='')
and
([B] like @B + '%' or @B='')
and
([c] like @C + '%' or @C='')
and
([D] like @D + '%' or @D ='')
and
([E] like @E = '%' or @E='')

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-10 : 11:18:20

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources