Professional Web Applications Themes

Output parameter - Microsoft SQL / MS SQL Server

I have a table Revision with 3 fields JobID, RevisionDate and RevisionNumber. 1. I'd like to get all records for a particular JobID. I can do it by this SP: CREATE PROCEDURE CoordinatorRevisions CurrentJob int AS SELECT TOP 100 PERCENT RevisionDate, RevisionNumber FROM Revision WHERE (JobID = CurrentJob) ORDER BY RevisionDate GO 2. I also would like to return the string containing # plus RevisionNumber plus space plus RevisionDate. RevisionDate and RevisionNumber from the last record of the previous Select statement. I have a problem with syntax for SP. I know that I have to declare output parameter, but how ...

  1. #1

    Default Output parameter

    I have a table Revision with 3 fields JobID, RevisionDate and
    RevisionNumber.
    1. I'd like to get all records for a particular JobID. I can do it by this
    SP:

    CREATE PROCEDURE CoordinatorRevisions
    CurrentJob int

    AS

    SELECT TOP 100 PERCENT RevisionDate, RevisionNumber
    FROM Revision
    WHERE (JobID = CurrentJob)
    ORDER BY RevisionDate
    GO

    2. I also would like to return the string containing # plus RevisionNumber
    plus space plus RevisionDate. RevisionDate and RevisionNumber from the last
    record of the previous Select statement.
    I have a problem with syntax for SP.
    I know that I have to declare output parameter, but how to fill it inside
    SP - no idea.

    Anybody help please
    Vlad


    Vlad Guest

  2. #2

    Default Re: Output parameter

    I am not sure, if I understood your requirement. Are you looking for
    something similar to:

    SELECT CAST(CurrentJob AS VARCHAR) + SPACE(1) +
    RevisionNumber + SPACE(1)
    CONVERT(VARCHAR, RevisionDate, 112)
    FROM Revision
    WHERE JobId = CurrentJob ;

    If this is not what you are looking for, please post your table DDLs, sample
    data & expected results.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: Output parameter

    Assuming the 'last record' as the row with the most recent RevisionDate
    value, you can do:

    CREATE PROCEDURE usp
    jobid INT,
    output VARCHAR(40) OUTPUT
    AS
    SELECT RevisionDate, RevisionNumber
    FROM Revision
    WHERE JobID = jobid ;
    -- Set the output parameter
    SET output = (
    SELECT '#' + CAST(RevisionNumber AS VARCHAR) +
    SPACE(1) + CONVERT(VARCHAR, RevisionDate, 101)
    FROM Revision r1
    WHERE r1.JobID = jobid
    AND r1.RevisionDate = (SELECT MAX(r2.RevisionDate)
    FROM Revision r2
    WHERE r1.JobID = r2.JobID));
    GO

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  4. #4

    Default Re: Output parameter

    Thank you very much, Anith
    I'll try your code.
    So far I wrote some ugly stuff myself:

    CREATE PROCEDURE CoordinatorRevisions
    CurrentJob int,
    LastRevision varchar(20) output

    AS
    Declare LastRevisionDate datetime
    SELECT TOP 100 PERCENT RevisionID, RevisionDate, RevisionNumber
    FROM Revision
    WHERE (JobID = CurrentJob) AND (RowDeleted <> 1) --4070
    ORDER BY RevisionDate

    SELECT TOP 1 LastRevision=RevisionNumber
    FROM Revision
    WHERE (JobID = CurrentJob) AND (RowDeleted <> 1)
    ORDER BY RevisionNumber DESC

    Select LastRevisionDate=RevisionDate FROM Revision
    WHERE (JobID = CurrentJob) AND (RowDeleted <> 1) --4070
    ORDER BY RevisionNumber DESC

    SELECT LastRevision='#'+LastRevision+' '
    +CONVERT(varchar,LastRevisionDate,101)

    GO


    Vlad

    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:O#buiUNRDHA.3880tk2msftngp13.phx.gbl...
    > Assuming the 'last record' as the row with the most recent RevisionDate
    > value, you can do:
    >
    > CREATE PROCEDURE usp
    > jobid INT,
    > output VARCHAR(40) OUTPUT
    > AS
    > SELECT RevisionDate, RevisionNumber
    > FROM Revision
    > WHERE JobID = jobid ;
    > -- Set the output parameter
    > SET output = (
    > SELECT '#' + CAST(RevisionNumber AS VARCHAR) +
    > SPACE(1) + CONVERT(VARCHAR, RevisionDate, 101)
    > FROM Revision r1
    > WHERE r1.JobID = jobid
    > AND r1.RevisionDate = (SELECT MAX(r2.RevisionDate)
    > FROM Revision r2
    > WHERE r1.JobID = r2.JobID));
    > GO
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Vlad Guest

  5. #5

    Default Re: Output parameter

    Yes, your SP is working too and it's much more elegant
    Thank you very much again
    Vlad

    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:O#buiUNRDHA.3880tk2msftngp13.phx.gbl...
    > Assuming the 'last record' as the row with the most recent RevisionDate
    > value, you can do:
    >
    > CREATE PROCEDURE usp
    > jobid INT,
    > output VARCHAR(40) OUTPUT
    > AS
    > SELECT RevisionDate, RevisionNumber
    > FROM Revision
    > WHERE JobID = jobid ;
    > -- Set the output parameter
    > SET output = (
    > SELECT '#' + CAST(RevisionNumber AS VARCHAR) +
    > SPACE(1) + CONVERT(VARCHAR, RevisionDate, 101)
    > FROM Revision r1
    > WHERE r1.JobID = jobid
    > AND r1.RevisionDate = (SELECT MAX(r2.RevisionDate)
    > FROM Revision r2
    > WHERE r1.JobID = r2.JobID));
    > GO
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Vlad Guest

Similar Threads

  1. #39707 [NEW]: Sending more then one parameter to SP will not return output parameters
    By aspen dot olmsted at alliance dot biz in forum PHP Bugs
    Replies: 2
    Last Post: December 1st, 10:49 PM
  2. Date Parameter For Saved Parameter Queries
    By melody in forum ASP Database
    Replies: 13
    Last Post: December 19th, 09:22 AM
  3. Replies: 1
    Last Post: October 10th, 06:11 PM
  4. output/input parameter
    By Kenny in forum Microsoft Access
    Replies: 0
    Last Post: July 8th, 11:33 PM
  5. Replies: 1
    Last Post: July 3rd, 05:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139