1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How do I trigger a stored procedure

Discussion in 'Software Development' started by wango, Dec 23, 2001.

Thread Status:
Not open for further replies.
Advertisement
  1. wango

    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
     
  2. Rockn

    Rockn

    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"
     
  3. wango

    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
     
  4. Rockn

    Rockn

    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.
     
  5. wango

    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
     
  6. Rockn

    Rockn

    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.
     
  7. wango

    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
     
  8. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/62709

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice