How do I trigger a stored procedure

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

wango

Thread Starter
Joined
Nov 13, 2001
Messages
74
I have 2 storeed procedure in SQL Server 7.o. One of them needs the other as in:

-- procedure to change the date format of records in the ClientDate tableCREATE PROCEDURE [sp_ConvertDate] AS DECLARE @monthNumAsString VARCHAR(2), @monthName VARCHAR(3), @clientDate VARCHAR(30), @keyFld INTDECLARE cursCD CURSOR FAST_FORWARD FORSELECT keyFld, clientDate FROM ClientDateOPEN cursCDFETCH NEXT FROM cursCD INTO @keyFld, @clientDateWHILE @@FETCH_STATUS = 0BEGIN -- Convert the month name to a string representing the month number SET @monthName = SUBSTRING(@clientDate, 3,3) EXEC @monthNumAsString = sp_MonthToNumberString @monthName -- Now update the string field in the DB UPDATE ClientDate SET clientDate = SUBSTRING(@clientDate,6,4) + @monthNumAsString + SUBSTRING(@clientDate,1,2) WHERE keyFld = @keyFld FETCH NEXT FROM cursCD INTO @keyFld, @clientDateENDCLOSE cursCD
DEALLOCATE cursCD

-- **************8
-- Procedure to convert a month as 'MMM' to a string value
-- representing the month numberCREATE PROCEDURE [sp_MonthToNumberString] ( @monthName varchar(3) )ASDECLARE @monthNumS varchar(2)IF @monthName = 'JAN' SET @monthNumS ='0 1'ELSE IF @monthName = 'FEB' SET @monthNumS =' 02'ELSE IF @monthName = 'MAR' SET @monthNumS = '03'ELSE IF @monthName = 'APR' SET @monthNumS = '04'ELSE IF @monthName = 'MAY' SET @monthNumS = '05'ELSE IF @monthName = 'JUN' SET @monthNumS = '06'ELSE IF @monthName = 'JUL' SET @monthNumS = '07'ELSE IF @monthName = 'AUG' SET @monthNumS = '08'ELSE IF @monthName = 'SEP' SET @monthNumS = '09'ELSE IF @monthName = 'OCT' SET @monthNumS = '10'ELSE IF @monthName = 'NOV' SET @monthNumS = '11'ELSE IF @monthName = 'DEC' SET @monthNumS = '12'ELSE SET @monthNumS = '00'RETURN @monthNumS


How do I go about triggering them?


What I am trying to do is to convert the date field "01DEC1993:09:57:32:49" to say "19931201"

monthNumAsString monthName clientDate keyFld
---------------- --------- ------------------------------ -----------
01 JAN 01DEC1993:09:57:32:49 1
01 JAN 01JAN1993:09:57:32:49 2
01 JAN 01FEB1993:09:57:32:49 2
01 JAN 01MAR1993:09:57:32:49 2
01 JAN 01APR1993:09:57:32:49 2

(5 row(s) affected)


Wango
 
Joined
Jul 29, 2001
Messages
21,334
What kind of front end are you using? If it's in ASP do something like this:

set connection = server.createobject("adodb.connection")
connection.open whateverDSN
Connection.Execute "ProcNamee varvalue1, varvalue2"
 

wango

Thread Starter
Joined
Nov 13, 2001
Messages
74
At one time I thought (wrongly may be and before I ever attempted to run one) that I can run stored procedures in say, the SQL Server query builder. If I cannot, then I can build something in C/C++ very quickly.

The question still remains, How do I run the main procedure [sp_ConvertDate] when it depends on another one
[sp_MonthToNumberString] ?

Wango
 
Joined
Jul 29, 2001
Messages
21,334
Seems like an awefully conveluded way to get a date formatted that way. WHat does the date format look like in the table itself when it is entered and what format is it in once it is converted into your custom string.
 

wango

Thread Starter
Joined
Nov 13, 2001
Messages
74
As was mentioned in the original thread,

********************************************
What I am trying to do is to convert the date field "01DEC1993:09:57:32:49" to say "19931201"
********************************************

the date looks like say,

"01DEC1993:09:57:32:49"

and the customer wants it to look like

"19931201"

Wango
 
Joined
Jul 29, 2001
Messages
21,334
Why don't you use an Trim function to first trim everything off to the right of the colon and then work with what's left over and it could all be done from one stored procedure.
 

wango

Thread Starter
Joined
Nov 13, 2001
Messages
74
I think you have a point; however there is more than one way to skin "Silvester". I have just run the "sp_ConvertDate" stored procedure form the "SQL Server Query Analyser" (straight in the database environment". I started out with:

monthNumAsString clientDate keyFld
---------------- ------------------------------ -----------
1 19931211 11
2 12DEC1993:09:57:32:49 12
3 13DEC1993:09:57:32:49 13
4 14DEC1993:09:57:32:49 14
5 15DEC1993:09:57:32:49 15
6 16DEC1993:09:57:32:49 16
7 17DEC1993:09:57:32:49 17
8 18DEC1993:09:57:32:49 18
9 19DEC1993:09:57:32:49 19
10 20DEC1993:09:57:32:49 20

I ended up with:

monthNumAsString clientDate keyFld
---------------- ------------------------------ -----------
1 19931211 11
2 19931212 12
3 19931213 13
4 19931214 14
5 19931215 15
6 19931216 16
7 19931217 17
8 19931218 18
9 19931219 19
10 19931220 20

(10 row(s) affected)

So, I guess the stored procedure that I am using works well. All my method is doing is to extract only what it needs from the long
"02DEC1993:09:57:32:49" [ClientDate] attribute.

If you have a more effient way, I will be glad to try it.

Rockn, what happened to the cascading combo box that you promised me!

Thanks and happy holidays,



Wango
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top