value can get through if-else condition...

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 2000 Forums

Transact-SQL (2000)

value can get through if-else condition...

Author

Topic

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-03-22 : 00:47:50

In table there is a field customer_name,and other field is dump_value.if customer_name field is blank then condition return dump_value will be 'Y' else 'no'

someone plsss help me to get the answer.DDl also given below....

alter proc transport_slip_report

--exec transport_slip_report

as
begin

DECLARE @company_no varchar(12),
@locn_no varchar(12)

SELECT @company_no = 'AAA',
@locn_no = 'HSYWN'

if exists(select '*'from CVCVCC
where company_no = @company_no
and locn_no = @locn_no )

begin

select CONVERT(char(20),challan_dt, 103)cha_date,
vehicle_no,
DOC_no,
noofbags,
challan_no,
challan_qty,
truck_owner,
freight_amt,
advance,
dest_name,
Cons_name,
Cons_addr1,
Cons_addr2,
Cons_addr3,
card_no ,
dump_value
from CVCVCC
where company_no = @company_no
and locn_no = @locn_no
and challan_dt = convert(datetime,convert(char(10),getdate() -1,101))

end

END

/***********************************************************************/
CREATE TABLE [dbo].[CVCVCC] (
[COMPANY_NO] [GLCOMPANY] NULL ,
[LOCN_NO] [GLLOCN] NULL ,
[KEY_VALUE] [varchar] (255) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[DOC_NO] [varchar] (25) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CHALLAN_NO] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CHALLAN_DT] [datetime] NULL ,
[DRIVE_NAME] [varchar] (60) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[LINCES_NO] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CARD_NO] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[VEHICLE_NO] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CUST_CODE] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CUST_NAME] [varchar] (60) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CUST_ADD1] [varchar] (200) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CUST_ADD2] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CUST_ADD3] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CONS_NAME] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CONS_ADDR1] [varchar] (200) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CONS_ADDR2] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CONS_ADDR3] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[DEST_NAME] [varchar] (60) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[FREIGHT_AMT] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CHALLAN_QTY] [numeric](13, 3) NULL ,
[TRUCK_OWNER] [varchar] (30) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[ADVANCE] [varchar] (30) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[PACK_TYPE] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[NOOFBAGS] [numeric](13, 0) NULL ,
[SPID] [int] NULL ,
[UNLOADING] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[dump_value] [varchar] (1) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO

/***********************************************************************/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-22 : 01:31:59

Do you need this?

select CONVERT(char(20),challan_dt, 103)cha_date,
vehicle_no,
DOC_no,
noofbags,
challan_no,
challan_qty,
truck_owner,
freight_amt,
advance,
dest_name,
Cons_name,
Cons_addr1,
Cons_addr2,
Cons_addr3,
card_no ,
case when CUST_NAME='' then 'Y' else 'No' end as dump_value,
from CVCVCC
where company_no = @company_no
and locn_no = @locn_no
and challan_dt = convert(datetime,convert(char(10),getdate() -1,101))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-03-22 : 06:27:57

thanks senthil i think that will work....thanks a lot...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-23 : 05:08:22

Not yet implemented? any way Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources