Assign multiple variables in a SP?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Assign multiple variables in a SP?

Hybrid View

  1. #1
    Shaun Higgins Guest

    Assign multiple variables in a SP?


    Hi all,

    Is it possible to use a recordset in a stored procedure, or at least assign
    multiple variables with one select statement? ie.
    SET @Var1, @Var2 = (SELECT Field1, Field2 FROM TableName WHERE ID = 7).

    I don't want to have to make multiple queries to the database. ie.
    SET @Var1 = SELECT Field1 FROM TableName WHERE ID = 7
    SET @Var2 = SELECT Field2 FROM TableName WHERE ID = 7
    etc etc.

    How do I handle the situation with multiple records? ie.
    SELECT Field1, Field2 from TableName WHERE ID <= 7

    Is any of this possible from within a stored procedure?

    If I've confused you: I want to be able to run a SELECT query and use the
    results within the SP (not return them to the calling app).

    Thanks
    Shaun

  2. #2
    Andrew Prosser Guest

    Re: Assign multiple variables in a SP?


    It depends on the version you are running, the following applies to all;

    To assign results to variables you can use the following for example;

    SELECT @date = getdate, @version = @@version, @field = col1 from table1

    OR

    SELECT @date = getdate, @version = @@version

    (ie. unlike Oracle you do not need a FROM clause and a pseudo table)

    SQL 2000 has new datatypes which I am not that familiar with but you might
    want to look at BOL for 'sql_variant' and 'table' datatypes'

    Totsiens

    "Shaun Higgins" <shiggins@businesspartners.co.za> wrote:
    >
    >Hi all,
    >
    >Is it possible to use a recordset in a stored procedure, or at least assign
    >multiple variables with one select statement? ie.
    >SET @Var1, @Var2 = (SELECT Field1, Field2 FROM TableName WHERE ID = 7).
    >
    >I don't want to have to make multiple queries to the database. ie.
    >SET @Var1 = SELECT Field1 FROM TableName WHERE ID = 7
    >SET @Var2 = SELECT Field2 FROM TableName WHERE ID = 7
    >etc etc.
    >
    >How do I handle the situation with multiple records? ie.
    >SELECT Field1, Field2 from TableName WHERE ID <= 7
    >
    >Is any of this possible from within a stored procedure?
    >
    >If I've confused you: I want to be able to run a SELECT query and use the
    >results within the SP (not return them to the calling app).
    >
    >Thanks
    >Shaun



  3. #3
    DaveSatz Guest

    Re: Assign multiple variables in a SP?

    try:

    SELECT @Var1 = Field1
    , @Var2 = Field2
    FROM TableName
    WHERE ID = 7

    if they are multiple rows you should be selecting into a variable, you would
    need to use a cursor possible to process each row:

    declare @var1 int, @var2 sysname
    SELECT @Var1 = id
    , @Var2 = type
    FROM sysobjects
    WHERE ID = 1

    SELECT @Var1, @Var2

    SELECT @Var1 = id
    , @Var2 = type
    FROM sysobjects
    WHERE ID >= 1

    SELECT @Var1, @Var2

    -- this is the results with multiple rows for id
    SELECT MAX(ID) FROM SYSOBJECTS
    go

    --
    HTH,
    David Satz
    Principal Software Engineer
    Hyperion Solutions
    ->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------

    "Shaun Higgins" <shiggins@businesspartners.co.za> wrote in message
    news:3a9f78da$1@news.devx.com...
    >
    > Hi all,
    >
    > Is it possible to use a recordset in a stored procedure, or at least

    assign
    > multiple variables with one select statement? ie.
    > SET @Var1, @Var2 = (SELECT Field1, Field2 FROM TableName WHERE ID = 7).
    >
    > I don't want to have to make multiple queries to the database. ie.
    > SET @Var1 = SELECT Field1 FROM TableName WHERE ID = 7
    > SET @Var2 = SELECT Field2 FROM TableName WHERE ID = 7
    > etc etc.
    >
    > How do I handle the situation with multiple records? ie.
    > SELECT Field1, Field2 from TableName WHERE ID <= 7
    >
    > Is any of this possible from within a stored procedure?
    >
    > If I've confused you: I want to be able to run a SELECT query and use the
    > results within the SP (not return them to the calling app).
    >
    > Thanks
    > Shaun




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center