Problem in search+case - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in search+case

Author

Topic

Dajer
Starting Member

22 Posts

Posted - 2009-09-09 : 03:14:50

Hi
I'm writing a search storedprocedure.I want when user enter Creditor it comes into the storedprocedure else I don't want to involve it in my storedprocedure.this is the storedprocedure that I have written:

@docdtl nvarchar(50)=null,
	@facnum nvarchar(50)=null,
	@creditor nvarchar(50)=null,
	@debtor nvarchar(50)=null,
	@office int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    select * from [document] where OfficeID=@office and (FacNum=''+@facnum+'''')
UNION
select * from [document] where OfficeID=@office and (DocDetails=''+@docdtl+'''')
UNION
select * from [document] where OfficeID=@office and Creditor=
CASE
when Creditor<>'' then  (convert(nvarchar(50),@creditor))
Else Null
END
select * from [document] where OfficeID=@office and (Creditor=convert(nvarchar(50),@creditor))
UNION
select * from [document] where OfficeID=@office and (Debtor=convert(nvarchar(50),@debtor))
END

but when I run it and don't enter the Creditor it shows the records that have a Ceditor with value 0.
what shuld I do?

Sara Dajer

Sachin.Nand

2937 Posts

Posted - 2009-09-09 : 05:20:01

declare @tbl as table(creditor varchar(10))
insert into @tbl
select 'a' union all
select 'a1'

declare @creditor as varchar(10)
select @creditor=null
select * from @tbl where creditor=case when @creditor is not null then @creditor else creditor end

PBUH

Dajer
Starting Member

22 Posts

Posted - 2009-09-09 : 05:32:35

I did it.but again when I don't enter creditor it brings the creditor that have value 0.

Sara Dajer

Sachin.Nand

2937 Posts

Posted - 2009-09-09 : 05:54:47

Please post some sample data.

PBUH

Dajer
Starting Member

22 Posts

Posted - 2009-09-09 : 06:01:24

@debtor=156000
resault:
Debtor Creditor
------ --------
156000 12000
144000 0
156000 0

as u see in second record we have Creditor=0(and in third record)

Sara Dajer

Sachin.Nand

2937 Posts

Posted - 2009-09-09 : 06:23:12

DECLARE @tbl AS TABLE(Debtor INT,Creditor INT)
INSERT INTO @tbl
SELECT 156000,12000 UNION ALL
SELECT 144000 ,0 UNION ALL
SELECT 156000 ,0

DECLARE @debtor AS VARCHAR(10)
SELECT @debtor=156000
DECLARE @creditor AS VARCHAR(10)
SELECT @creditor=NULL

SELECT * FROM @tbl WHERE Debtor=CASE WHEN @debtor IS NOT NULL THEN
@debtor
ELSE
Debtor
END
AND CASE WHEN Creditor=0 THEN NULL ELSE Creditor END=CASE WHEN @creditor IS NOT NULL THEN
@creditor
ELSE
Creditor
END

But what is the table is lk this what shud be the resultset?

DECLARE @tbl AS TABLE(Debtor INT,Creditor INT)
INSERT INTO @tbl
SELECT 156000,12000 UNION ALL
SELECT 144000 ,0 UNION ALL
SELECT 156000 ,1

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources