Professional Web Applications Themes

Return sp's result row into local variables? - Microsoft SQL / MS SQL Server

declare Alpha varchar(50), Beta varchar(5), declare Gamma int CREATE TABLE #abg(a VARCHAR(50), b VARCHAR(5), c INT) INSERT #abg EXEC FooBarBeyondAllCompare 666 SELECT Alpha = a, Beta = b, Gamma = c FROM #abg DROP TABLE #abg Also see http://www.algonet.se/~sommar/share_data.html "Ian Boyd" <com> wrote in message news:O1T$phx.gbl...  and ...

  1. #1

    Default Re: Return sp's result row into local variables?

    declare Alpha varchar(50), Beta varchar(5), declare Gamma int
    CREATE TABLE #abg(a VARCHAR(50), b VARCHAR(5), c INT)
    INSERT #abg EXEC FooBarBeyondAllCompare 666
    SELECT Alpha = a, Beta = b, Gamma = c FROM #abg
    DROP TABLE #abg

    Also see http://www.algonet.se/~sommar/share_data.html




    "Ian Boyd" <com> wrote in message
    news:O1T$phx.gbl... 
    and 


    Aaron Guest

  2. #2

    Default Re: Return sp's result row into local variables?

    Ian,


    CREATE PROCEDURE FooBarBeyondAllCompare a varchar(50) output,b varchar(5)
    output,c int output AS
    SELECT a = 'one', b = 'two', c = 3
    GO
    CREATE PROCEDURE IHaveAHeadache AS

    declare Alpha varchar(50)
    declare Beta varchar(5)
    declare Gamma int
    EXEC dbo.FooBarBeyondAllCompare Alpha output, Beta output, Gamma output
    Select Alpha, Beta, Gamma
    Go
    EXEC dbo.IHaveAHeadache


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Ian Boyd" <com> wrote in message
    news:O1T$phx.gbl... 
    and 


    Dinesh.T.K Guest

  3. #3

    Default Re: Return sp's result row into local variables?

    i understand your reply - thank you.

    i don't understand that page.


    "Aaron Bertrand - MVP" <com> wrote in message
    news:%phx.gbl... 
    > and 
    >
    >[/ref]


    Ian Guest

  4. #4

    Default Re: Return sp's result row into local variables?

    Ian,

    Insert the results from the satored procedure into a local #temp
    table and then assign to variables form there:


    CREATE PROCEDURE IHaveAHeadache AS

    declare Alpha varchar(50)
    declare Beta varchar(5)
    declare Gamma int

    create table #tmp (
    Alpha varchar(50),
    Beta varchar(5),
    Gamma int,
    )
    insert #tmp FooBarBeyondAllCompare 666

    select top 1 Alpha = alpha,
    Beta = Beta,
    Gamma = Gamma
    from #tmp


    Or you can use output parameters to return the values:

    CREATE PROCEDURE FooBarBeyondAllCompare Parameter int
    Alpha varchar(50) output,
    Beta varchar(5) output,
    Gamma int output
    AS
    select Alpha = 'one',
    Beta = 'two',
    Gamma = 3
    return 0


    Linda

    lindawie Guest

Similar Threads

  1. Replies: 14
    Last Post: December 19th, 11:58 AM
  2. How to populate tree with result of webservice return?
    By jj3001 in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: September 7th, 01:16 AM
  3. multilined regexp won't return result
    By perl@mortesicura.de in forum PERL Beginners
    Replies: 1
    Last Post: November 14th, 03:34 PM
  4. How to get php.exe to return a correct result code
    By Chris John Jordan in forum PHP Development
    Replies: 1
    Last Post: September 20th, 12:16 PM
  5. Making PHP return SQL result to a different page
    By David Walker in forum PHP Development
    Replies: 4
    Last Post: June 30th, 09:19 AM

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