how to customize the no. in between

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)

how to customize the no. in between

Author

Topic

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2009-07-16 : 23:28:47

hi to all...
i would like to know how to customize the registration_no within some numbers...
i m having one uds screen...

there are two coding part...
1st is........HEADER APPLY STORED PROCEDURE NAME
CREATE PROCEDURE em_vend_det_hdr_ins11
( @udsid char(10),
@screenid varchar(10),
@company_code glcompany,
@location_code gllocn,
@langid smallint,
@mode tinyint,
@key_value varchar(255),
@vendor_no char(22),
@register_no char(50)

)
AS
BEGIN

if ( LEN (@register_no) < 15)
begin
select '88888','error'
end
else
begin
select '99999','good'
end
END

and 2nd one from fetching the data is.....HEADER FETCH STORED PROCEDURE NAME...

CREATE PROCEDURE em_vend_det_hdr_sel
( @udsid char(10),
@screenid varchar(10),
@company_code glcompany,
@location_code gllocn,
@langid smallint,
@mode tinyint,
@key varchar(255),
@vendor_no char(16)

)
AS
BEGIN
DECLARE @excise_reg BIT,
@user_id cmn_userid

SELECT @user_id = user_id()

EXEC @excise_reg = common..em_interface_activation_check
@company_code ,
@location_code ,
@langid ,
@user_id ,
'POM' ,
'B'

IF (@excise_reg = 0) /* IF EM Nor Registered Do Not Launch The Screen */
BEGIN
SELECT '88888','EM Not Registered'
RETURN
END

DECLARE @pos int,
@len_temp int,
@count_temp int

SELECT @key = NULLIF(RTRIM(@key),'')
SELECT @vendor_no = NULLIF(RTRIM(@vendor_no),'')

SELECT @len_temp = DATALENGTH(@key)

IF (@len_temp = 0)
BEGIN
SELECT @len_temp = 1
END

SELECT @count_temp = 0
WHILE (@count_temp < 3)
BEGIN
SELECT @pos = CHARINDEX('~', ISNULL(@key,''))
SELECT @key = SUBSTRING(@key, (@pos + 1), (@len_temp - @pos))
SELECT @count_temp = @count_temp + 1
END

IF EXISTS (SELECT 'X'
FROM common..em_cust_ven_stax_detail
WHERE company_code = @company_code
AND locn_no = @location_code
AND cust_ven_flag = 'V'
AND cust_ven_no = @vendor_no)
BEGIN
SELECT '99999',1001,cust_ven_no,1002,register_no
FROM common..em_cust_ven_stax_detail
WHERE company_code = @company_code
AND locn_no = @location_code
AND cust_ven_flag = 'V'
AND cust_ven_no = @vendor_no
GROUP by cust_ven_no,register_no
END

END
how to customize the registration_no greater than 10 and less than 50
and if both the condition will broke give error.........
table value is ...
em_cust_ven_stax_detail
cust_ven_no SERVICE_HEAD REGISTER_NO
---------- ------------ ------------
V
V
V
THE 2ND TABLE IS
em_interface_activation_check---
PARAMETER_NAME
@company_code
@location_code
@lang_code
@user_id
@base_app_code
@be_fe_flag

PARAMETER_TYPE
--------------
GLCOMPANY
GLLOCN
cmn_langid
cmn_userid
cmn_app_code
cmn_flag

pls tell me how to solve me out..

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2009-07-17 : 02:40:24

ALTER PROCEDURE em_vend_det_hdr_ins1
( @udsid char(10),
@screenid varchar(10),
@company_code glcompany,
@location_code gllocn,
@langid smallint,
@mode tinyint,
@key_value varchar(255),
@vendor_no char(22),
@from_register_no char(50),
@to_register_no char(50)

)
AS
BEGIN
set nocount on
--if ( LEN (@register_no) < 15)
-- begin
--select '88888','hello'
--end
-- else
if (len (@from_register_no)< 15) -- or (@from_register_no)IS null
begin
select @from_register_no ='88888' --,'registration_no should be more than 15 or lessdigits'
end
else

if (len(@to_register_no)> 50) --or @to_register_no IS null
BEGIN
select @to_register_no ='99999' --,'registration_no should be less than 50 digits'
end
--SELECT * FROM em_vend_det_hdr_ins1
--RETURN END

create table #ven_ser
(
company_code glcompany,
location_code gllocn,
registration_no char(50)
)

insert into (company_code,location_code,registration_no)
select company_code,location_code,registration_no
from common..em_cust_ven_stax_detail
WHERE company_code = @company_code
AND locn_no = @location_code
AND cust_ven_flag = 'V'
AND cust_ven_no = @vendor_no
ORDER by register_no

delete from #ven_ser
where (registration_no < @from_register_no or registration_no > @to_register_no)

set nocount off

END
-- Line 49: Incorrect syntax near '('.
I WOULD LIKE TO CUSTOMIZE THE REGISTRATION NUMBER.....IN BETWEEN 15 TO 50

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources