Help with query . . . .. -- sql server mseq

sql server mseq : Help with query . . . ..

Newbie
10/21/2003 3:39:32 PM

Hi

I have a parameter @Status in a stored procedure

How can I achieve the following

a). return all records or
b) return only records that are = 'N' or null or blank

I thought about
where compflag = coalesce(@status,compflag)

but this doesn't cater for both 'N' and null records

what do you suggest I should do?

Newbie
10/21/2003 4:35:35 PM

Thanks but I am not sure I understand what you mean . . :-(
I call this stored procedure from vb

What I am trying to achieve is the following:
compflag has 4 possible values:
'Y', 'N', null, blank

I want to be able to return either
a) all records i.e 'Y', 'N', null, blank or,
b) records that = 'N', null or blank

all from one query if possible

what do I need to do?

Thanks again for your help

[quoted text, click to view]

Newbie
10/21/2003 5:03:21 PM

table 1
PONo
LineNum
CompFlag
Stockcode

Record 1
PONo:R12345
LineNum:1
CompFlag:Y
Stockcode:PartABC

Record 2
PONo: R98765
LineNum: 1
CompFlag: N
Stockcode:PartABC

Record 3
PONo: S34567
LineNum: 1
CompFlag: null
Stockcode: PartHIJ

Record 4
PONo: S34567
LineNum: 2
CompFlag: ''
Stockcode: PartHIJ

On my vb form I have checkbox - incl. completed orders
If this is ticked I want to return all records
i.e Records 1,2,3,4

If it isn't ticked I want to return records 2, 3 and 4 i.e all records
where CompFlag <> 'Y'

[quoted text, click to view]

Vishal Parkar
10/21/2003 8:43:30 PM

Newbie,

[quoted text, click to view]

The above condition will check value of @status if it is not null then it will always return this
value.

You can put condition as

declare @status char(1)
set @status='N'
select * from table
where (col1 = @status or col1 is null or ltrim(col1) = '')

--
- Vishal

Vishal Parkar
10/21/2003 9:10:41 PM

Newbie,
can you post table structure some sample records, whether you are passing any parameter to query and
if yes then on the basis of value from this parameter what records should be retrieved.

--
- Vishal

Vishal Parkar
10/21/2003 10:33:00 PM

Newbie,
See following example

create table #t(PONo varchar(25),LineNum varchar(25),CompFlag char(1),Stockcode varchar(50))

insert into #t
select 'R12345',1,'Y','PartABC' union all
select 'R98765', 1, 'N','PartABC' union all
select 'S34567', 1, null,'PartHIJ' union all
select 'S34567', 2, '','PartHIJ'

--Required script would be.

declare @checked char(1)
set @checked='Y' --if checkebox is checked pass a variable value as 'Y' to query
if @checked = 'Y'
select * from #t
Else
select * from #t where (compflag <> 'Y' or compflag is null)

--
- Vishal

Newbie
10/22/2003 12:54:01 PM

thanks - works a treat
Al

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

View Other Months
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn