Icon View Thread

The following is the text of the current message along with any replies.
Messages 11 to 20 of 26 total
Thread Help Converting MS SQL Stored Procedure To ElevateDB
Tue, Sep 6 2011 7:42 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Michael


See my response to John. Looking at what you've posted you're trying to insert a DATE variable into a DATE column and if that doesn't work you've screwed up somewhere else. Can you post your database and code into the binaries ng and I'll have a fiddle.

Roy Lambert [Team Elevate]
Tue, Sep 6 2011 9:42 AMPermanent Link

Michael Riley

ZilchWorks

Avatar

Roy

>>
Err why are you casting things to varchar?
<<

I thought EXECUTE IMMEDIATE executed dynamic sql.

How do you write an EXECUTE IMMEDIATE statement that does the following:

DECLARE MyDate DATE;
SET MyDate = CURRENT_DATE + INTERVAL '1' MONTH;

EXECUTE IMMEDIATE '
INSERT INTO MYTABLE
( MyTableDate)
Values
( ''' + CAST(MyDate as varchar(20)) + ''' )
'

If this is not the proper way to perform an insert into a database table from inside a stored procedure then PLEASE show me the correct way to do this. I'm getting very frustrated.

I feel like I'm sitting in  the cockpit of a plane looking at the controls for THE FIRST TIME and all I hear is "Why did you push that button?" or "Why did you move that lever?" or "Have you RTFM?"

I could not find one example of how to do this in any of the manuals or forum posts. If I did, I wouldn't be asking here. Smile

Roy, I'm already on the ground stop kicking me. SHOW ME how to do this or if you do not know how to do this then just say so.

Michael Riley
GySgt USMC Retired
www.zilchworks.com
Tue, Sep 6 2011 10:17 AMPermanent Link

Michael Riley

ZilchWorks

Avatar

Roy

>>
See my response to John. Looking at what you've posted you're trying to insert a DATE variable into a DATE column and if that doesn't work you've screwed up somewhere else. Can you post your database and code into the binaries ng and I'll have a fiddle.
<<

There are no binaries. I'm doing all of this inside the ElevateDB Manager.
I'll figure it out after I get home from work.

Michael Riley
GySgt USMC Reried
www.zilchworks.com
Tue, Sep 6 2011 10:20 AMPermanent Link

Malcolm Taylor

Michael Riley wrote:

> I don't understand what you mean. If you could post a piece of sample
> code that would be extrremely helpful.

Michael, this has caught out a lot of users so don't feel too bad.

From the SQL Help uinder Date and Time Types, see the Literals section.
Specifically:
   SET "MyDateCol" = DATE '2011-09-06'

Note the keyword DATE
Tue, Sep 6 2011 10:29 AMPermanent Link

John Hay

Michael

> I don't understand what you mean. If you could post a piece of sample
> code that would be extrremely helpful.

The following should work (if embedded quotes are right Smiley

 EXECUTE IMMEDIATE '
 INSERT INTO TempAmortization
 (
 PmtNumber     ,
--  PmtDate       ,
 PmtAmount     ,
 BalanceStart  ,
 PmtPrincipal  ,
 PmtInterest   ,
 BalanceEnd
 )
 VALUES
 (
 '   + CAST(PmtNumber    as varchar(25)) + '   ,date
 ''' + CAST(PmtDate      as varchar(25)) + ''' ,
 '   + CAST(Payment      as varchar(25)) + '   ,
 '   + CAST(BalanceStart as varchar(25)) + '   ,
 '   + CAST(PmtPrincipal as varchar(25)) + '   ,
 '   + CAST(PmtInterest  as varchar(25)) + '   ,
 '   + CAST(BalanceEnd   as varchar(25)) + '
 )
 ';

John

Tue, Sep 6 2011 10:58 AMPermanent Link

Raul

Team Elevate Team Elevate

Michael,

I think Roy was asking why do you store them as varchars? Assuming that's what you need to do it's fine but you could store them as DATE columns and then use procedure to insert values as dates.

For example if i had a imaginary table called MyTable that had 2 columns called NextPayment,ReviewDate and both were of DATE datatype then following would insert 2 date values - 1 for current date+1month and 1 literal date string for Dec 31,2011 :

EXECUTE IMMEDIATE 'INSERT INTO MyTable (NextPayment,ReviewDate) VALUES (CURRENT_DATE + INTERVAL ''1'' MONTH, DATE ''2011-12-31'');';

Raul


Michael Riley wrote:

How do you write an EXECUTE IMMEDIATE statement that does the following:

DECLARE MyDate DATE;
SET MyDate = CURRENT_DATE + INTERVAL '1' MONTH;

EXECUTE IMMEDIATE '
INSERT INTO MYTABLE
( MyTableDate)
Values
( ''' + CAST(MyDate as varchar(20)) + ''' )
'

If this is not the proper way to perform an insert into a database table from inside a stored procedure then PLEASE show me the correct way to do this. I'm getting very frustrated.

I feel like I'm sitting in  the cockpit of a plane looking at the controls for THE FIRST TIME and all I hear is "Why did you push that button?" or "Why did you move that lever?" or "Have you RTFM?"

I could not find one example of how to do this in any of the manuals or forum posts. If I did, I wouldn't be asking here. Smile

Roy, I'm already on the ground stop kicking me. SHOW ME how to do this or if you do not know how to do this then just say so.

Michael Riley
GySgt USMC Retired
www.zilchworks.com
Tue, Sep 6 2011 11:05 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Michael

>I feel like I'm sitting in the cockpit of a plane looking at the controls for THE FIRST TIME and all I hear is "Why did you push that button?" or "Why did you move that lever?" or "Have you RTFM?"

My apologies, I missed the fact that you were using EXECUTE IMMEDIATE

>I could not find one example of how to do this in any of the manuals or forum posts. If I did, I wouldn't be asking here. Smile

There are, but as with most manuals, OLH or forums you have to know what you're looking for first. There are a lot of times I've asked something on the CodeGear ngs to have someone give me a search string for Google that gave me what I wanted after I'd spent hours searching fruitlessly.

>Roy, I'm already on the ground stop kicking me. SHOW ME how to do this or if you do not know how to do this then just say so.

I'm not kicking, I just missed the key bit of information. First as John's example you'll need the DATE keyword in front of the date as string. Secondly, since you've already asked about best practice, I'd say best practice is not to use EXECUTE IMMEDIATE within a loop with 360 iterations. Something like

SCRIPT
BEGIN

DECLARE Ins SENSITIVE CURSOR FOR Stmt;
DECLARE Looper INTEGER;
PREPARE Stmt FROM 'SELECT * FROM Fred';
OPEN Ins;

SET Looper = 1;
WHILE Looper < 10 DO
INSERT INTO Ins VALUES(CAST(Looper AS VARCHAR(12)),'xx');
SET Looper = Looper + 1;
END WHILE;

END

would probably be better.

And just to prove there is stuff in the manual Smiley

CREATE PROCEDURE UpdateState()
BEGIN
DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
DECLARE State CHAR(2) DEFAULT '';
PREPARE Stmt FROM 'SELECT * FROM Customer';
OPEN CustCursor;
FETCH FIRST FROM CustCursor ('State') INTO State;
WHILE NOT EOF(CustCursor) DO
IF (State = 'FL') THEN
UPDATE CustCursor SET 'State' = 'NY';
END IF;
FETCH NEXT FROM CustCursor ('State') INTO State;
END WHILE;
END

OK its update rather than insert but its the one I remembered about.

Roy Lambert [Team Elevate]
Tue, Sep 6 2011 11:08 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Raul


The problem was I had a touch of blindness and missed the EXECUTE IMMEDIATE.

Roy Lambert
Tue, Sep 6 2011 12:20 PMPermanent Link

Michael Riley

ZilchWorks

Avatar

>>
When using a date literal you need to qualify it ie date'2011-09-15' instead of just '2011-09-15'
<<

John,

Thank you. I didn't know i needed the "Date" keyword. Smile
Michael Riley
GySgt USMC Retired
www.zilchworks.com
Tue, Sep 6 2011 12:24 PMPermanent Link

Michael Riley

ZilchWorks

Avatar

"Malcolm" wrote:

>>
Michael, this has caught out a lot of users so don't feel too bad.

From the SQL Help uinder Date and Time Types, see the Literals section.
Specifically:
   SET "MyDateCol" = DATE '2011-09-06'

Note the keyword DATE
<<

Thank you Malcom. I will check that out when I get home from work.
Michael Riley
GySgt USMC Retired
www.zilchworks.com
« Previous PagePage 2 of 3Next Page »
Jump to Page:  1 2 3
Image