Problem with Not Null....!!!!![Very urgrnt pls???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Problem with Not Null....!!!!![Very urgrnt pls????

Author

Topic

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-07-15 : 02:48:42

hi all i got one problem..
i am doing conversion of Oravcle SPs into Sql Server SPs.
Where in Oracle they are declared one variable like...

v_num number(10) not null.
but i declared that into SqlServer like..

@v_num int not null..

but it shows the error at the not null..
can yoiu giv me the solution very urgrnt pls

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-07-15 : 03:08:03

remove not null

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-07-15 : 03:11:31

HI THANK U VERY MUCH ...
ONE MORE PROBS...

IN ORACLE THEY ARE USING "SYSDATE"
EQUALENT PLS IN SQL SERVER

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-15 : 03:21:07

GETDATE()

Andy

Beauty is in the eyes of the beerholder

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-07-15 : 03:30:36

hi andy..!!

i am using that getdate() but still my SP shows this error..
The name 'getdate' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

actually iam trying to insert that todays date into the some table ...
can you giv me thE SOL PLS

THANKS ®ARDS
RAMANA

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-07-15 : 03:34:00

Why don't you show us your code first, so we're not trying to guess.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 03:34:53

Can you post the full code you are trying to use?

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-07-15 : 03:39:20

HI THIS IS MY Sp CODE PLS HELP ME OUT FORM THIS sp PLS IAM NOVICE TO SQLSERVER

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-07-15 : 03:40:02

HI THISA IS THE SP CODE WHICH I AM USING....
-------------------------------------------------

CREATE PROCEDURE Registrations_PKG_DELETE_REG (
@IN_REGISTRATION_CODE INT)
WITH ENCRYPTION
AS

DECLARE fee_cur CURSOR FOR
select * from fees_list fees
where reg_registration_code = @IN_REGISTRATION_CODE
and fes_record_type = 'F'
and status = 'F'
and fes_fee_type = (select fee_type_code
from fee_types
where fee_type_code = fees.fes_fee_type
and tuition_type_fee = 'R');

DECLARE @ParamValue VARCHAR(255),
@v_amount NUMERIC(15,2),
@v_fee_record_code INT;
DECLARE @Fee_RECORD_Code INT,
@CALOCC_Calendar_Type VARCHAR (10),
@CALOCC_Occurrence_Code VARCHAR(10),
@AMOUNT NUMERIC(15,2),
@Fee_Date DATE ,
@Description VARCHAR(240),
@Created_By VARCHAR(30 ),
@Created_Date DATE,
@SFT_Student_Fee_REF INT,
@Status VARCHAR(1) ,
@Fes_Person_Code INT ,
@Fes_record_Type VARCHAR(1) ,
@Fes_uins_Code VARCHAR(10),
@Fes_UIO_cType VARCHAR(10),
@Fes_UIO_calocc VARCHAR(10),
@Fes_Fee_Type VARCHAR(6) ,
@Fes_who_to_pay VARCHAR(10) ,
@Fes_bal_to_update INT ,
@Fes_Sponsor_Code VARCHAR(30),
@Fes_tax_flag VARCHAR(1),
@Fes_vat_amount NUMERIC(7,2),
@Fes_vat_Code VARCHAR(2),
@Fes_vat_rate NUMERIC (6,2),
@Fes_Funding_Year VARCHAR(10),
@Fee_Value_INT INT(10);
BEGIN

-- Look up institution details parameter value, for REG_FEE_DEL_OPT parameter.
EXEC dbo.GetParameterValue 'REG_FEE_DEL_OPT',@ParamValue;

IF @ParamValue = 'C'
BEGIN
select @v_amount=sum(amount)
from fees_list
where reg_registration_code = @IN_REGISTRATION_CODE;

IF @v_amount != 0
BEGIN
WHILE (@@FETCH_STATUS<>0)
BEGIN
-- For fee_rec in fee_cur Loop
-- select fee_list_seq.nextval
--into v_fee_record_code
-- from sys.dual;
select @v_fee_record_code=fee_list_seq.nextval
from sys.dual;

insert into FEES_LIST (
FEE_RECORD_CODE,
CALOCC_CALENDAR_TYPE,
CALOCC_OCCURRENCE_CODE,
AMOUNT,
FEE_DATE,
DESCRIPTION,
CREATED_BY,
CREATED_DATE,
SFT_STUDENT_FEE_REF,
STATUS,
FES_PERSON_CODE,
FES_RECORD_TYPE,
FES_UINS_CODE,
FES_UIO_CTYPE,
FES_UIO_CALOCC,
FES_FEE_TYPE,
FES_WHO_TO_PAY,
FES_BAL_TO_UPDATE,
FES_SPONSOR_CODE,
FES_TAX_FLAG,
FES_VAT_AMOUNT,
FES_VAT_CODE,
FES_VAT_RATE,
FES_FUNDING_YEAR,
FEE_VALUE_INT )
values (
@Fee_RECORD_Code,
@CALOCC_CALENDAR_TYPE,
@CALOCC_OCCURRENCE_CODE,
@AMOUNT,
dbo.getdate,
@DESCRIPTION,
SYSTEM user,
sysdate,
@SFT_STUDENT_FEE_REF,
@STATUS,
@FES_PERSON_CODE,
@FES_RECORD_TYPE,
@FES_UINS_CODE,
@FES_UIO_CTYPE,
@FES_UIO_CALOCC,
@FES_FEE_TYPE,
@FES_WHO_TO_PAY,
@FES_BAL_TO_UPDATE,
@FES_SPONSOR_CODE,
@FES_TAX_FLAG,
- @FES_VAT_AMOUNT,
@FES_VAT_CODE,
@FES_VAT_RATE,
@FES_FUNDING_YEAR,
@FEE_VALUE_INT
);

END
END

update FEES_LIST set REG_REGISTRATION_CODE=null,updated_date=sysdate,updated_by=system user
where reg_registration_code = @IN_REGISTRATION_CODE ;
END
ELSE
IF @ParamValue = 'D'
BEGIN
Delete from fees_list
where reg_registration_code =@IN_REGISTRATION_CODE;
END

-- Delete all progress records related to that registrations.
delete from progress_records
where reg_registration_code = @IN_REGISTRATION_CODE;

END

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-07-15 : 03:40:08

HI THISA IS THE SP CODE WHICH I AM USING....
-------------------------------------------------

CREATE PROCEDURE Registrations_PKG_DELETE_REG (
@IN_REGISTRATION_CODE INT)
WITH ENCRYPTION
AS

DECLARE fee_cur CURSOR FOR
select * from fees_list fees
where reg_registration_code = @IN_REGISTRATION_CODE
and fes_record_type = 'F'
and status = 'F'
and fes_fee_type = (select fee_type_code
from fee_types
where fee_type_code = fees.fes_fee_type
and tuition_type_fee = 'R');

DECLARE @ParamValue VARCHAR(255),
@v_amount NUMERIC(15,2),
@v_fee_record_code INT;
DECLARE @Fee_RECORD_Code INT,
@CALOCC_Calendar_Type VARCHAR (10),
@CALOCC_Occurrence_Code VARCHAR(10),
@AMOUNT NUMERIC(15,2),
@Fee_Date DATE ,
@Description VARCHAR(240),
@Created_By VARCHAR(30 ),
@Created_Date DATE,
@SFT_Student_Fee_REF INT,
@Status VARCHAR(1) ,
@Fes_Person_Code INT ,
@Fes_record_Type VARCHAR(1) ,
@Fes_uins_Code VARCHAR(10),
@Fes_UIO_cType VARCHAR(10),
@Fes_UIO_calocc VARCHAR(10),
@Fes_Fee_Type VARCHAR(6) ,
@Fes_who_to_pay VARCHAR(10) ,
@Fes_bal_to_update INT ,
@Fes_Sponsor_Code VARCHAR(30),
@Fes_tax_flag VARCHAR(1),
@Fes_vat_amount NUMERIC(7,2),
@Fes_vat_Code VARCHAR(2),
@Fes_vat_rate NUMERIC (6,2),
@Fes_Funding_Year VARCHAR(10),
@Fee_Value_INT INT(10);
BEGIN

-- Look up institution details parameter value, for REG_FEE_DEL_OPT parameter.
EXEC dbo.GetParameterValue 'REG_FEE_DEL_OPT',@ParamValue;

IF @ParamValue = 'C'
BEGIN
select @v_amount=sum(amount)
from fees_list
where reg_registration_code = @IN_REGISTRATION_CODE;

IF @v_amount != 0
BEGIN
WHILE (@@FETCH_STATUS<>0)
BEGIN
-- For fee_rec in fee_cur Loop
-- select fee_list_seq.nextval
--into v_fee_record_code
-- from sys.dual;
select @v_fee_record_code=fee_list_seq.nextval
from sys.dual;

insert into FEES_LIST (
FEE_RECORD_CODE,
CALOCC_CALENDAR_TYPE,
CALOCC_OCCURRENCE_CODE,
AMOUNT,
FEE_DATE,
DESCRIPTION,
CREATED_BY,
CREATED_DATE,
SFT_STUDENT_FEE_REF,
STATUS,
FES_PERSON_CODE,
FES_RECORD_TYPE,
FES_UINS_CODE,
FES_UIO_CTYPE,
FES_UIO_CALOCC,
FES_FEE_TYPE,
FES_WHO_TO_PAY,
FES_BAL_TO_UPDATE,
FES_SPONSOR_CODE,
FES_TAX_FLAG,
FES_VAT_AMOUNT,
FES_VAT_CODE,
FES_VAT_RATE,
FES_FUNDING_YEAR,
FEE_VALUE_INT )
values (
@Fee_RECORD_Code,
@CALOCC_CALENDAR_TYPE,
@CALOCC_OCCURRENCE_CODE,
@AMOUNT,
dbo.getdate,
@DESCRIPTION,
SYSTEM user,
sysdate,
@SFT_STUDENT_FEE_REF,
@STATUS,
@FES_PERSON_CODE,
@FES_RECORD_TYPE,
@FES_UINS_CODE,
@FES_UIO_CTYPE,
@FES_UIO_CALOCC,
@FES_FEE_TYPE,
@FES_WHO_TO_PAY,
@FES_BAL_TO_UPDATE,
@FES_SPONSOR_CODE,
@FES_TAX_FLAG,
- @FES_VAT_AMOUNT,
@FES_VAT_CODE,
@FES_VAT_RATE,
@FES_FUNDING_YEAR,
@FEE_VALUE_INT
);

END
END

update FEES_LIST set REG_REGISTRATION_CODE=null,updated_date=sysdate,updated_by=system user
where reg_registration_code = @IN_REGISTRATION_CODE ;
END
ELSE
IF @ParamValue = 'D'
BEGIN
Delete from fees_list
where reg_registration_code =@IN_REGISTRATION_CODE;
END

-- Delete all progress records related to that registrations.
delete from progress_records
where reg_registration_code = @IN_REGISTRATION_CODE;

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 03:49:59

that should be getdate()

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-07-15 : 04:02:12

hi,, thanku very much...
what i need to do for the "user"...field

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 04:18:07

Instead of system user use system_user

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-15 : 06:48:26

Do consider moving away from the cursor.....a straight line-by-line conversion from oracle -> sqlserver is not necessarily the best decision. A set-based solution should be achieveable for far better performance.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-15 : 07:30:41

See if this helps: http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources