How do I trigger a stored procedure

1101 Views 6 Replies 2 Participants Last post by wango, Dec 24, 2001

wango

Discussion Starter · Dec 23, 2001

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

See less See more

Save

Status: Not open for further replies.

1 - 7 of 7 Posts

Rockn

· #2 · Dec 24, 2001

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"

Save

wango

Discussion Starter · #3 · Dec 24, 2001

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

Save

Rockn

· #4 · Dec 24, 2001

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.

Save

wango

Discussion Starter · #5 · Dec 24, 2001

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

See less See more

Save

Rockn

· #6 · Dec 24, 2001

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.

Save

wango

Discussion Starter · #7 · Dec 24, 2001

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

See less See more

Save

1 - 7 of 7 Posts

Status: Not open for further replies.

Top Contributors this Month