Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Unanswered: variables inside a stored procedure

    Hey
    I need to use a variable in a stored procedure that I get from a select statement. is it possible to do this inside the stored procedure? My procedure looks kind of like this.

    CREATE PROCEDURE sp_2
    @IDnr int,
    @placekey varchar(100)

    As

    Select placeID from place2
    where placekey = @placekey
    execute sp_myProcedure @IDnr, @placeID, @placekey
    GO

    How do I get the value from the select statement into the variable @placeid?

    Thanks, Helena

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140

    Re: variables inside a stored procedure

    Originally posted by Helena
    Hey
    I need to use a variable in a stored procedure that I get from a select statement. is it possible to do this inside the stored procedure? My procedure looks kind of like this.

    CREATE PROCEDURE sp_2
    @IDnr int,
    @placekey varchar(100)

    As

    Select placeID from place2
    where placekey = @placekey
    execute sp_myProcedure @IDnr, @placeID, @placekey
    GO

    How do I get the value from the select statement into the variable @placeid?

    Thanks, Helena
    In your procedure, define an additional variable @placeId (which you need anyway to call sp_myprocedure), and change your select as follows :
    Select placeID INTO @PLACEID
    from place2
    where placekey = @placekey

    CVM.

  3. #3
    Join Date
    Sep 2003
    Posts
    212

    Re: variables inside a stored procedure

    Originally posted by Helena
    Hey
    I need to use a variable in a stored procedure that I get from a select statement. is it possible to do this inside the stored procedure? My procedure looks kind of like this.

    CREATE PROCEDURE sp_2
    @IDnr int,
    @placekey varchar(100)

    As

    Select placeID from place2
    where placekey = @placekey
    execute sp_myProcedure @IDnr, @placeID, @placekey
    GO

    How do I get the value from the select statement into the variable @placeid?

    Thanks, Helena
    declare @placeID
    declare CursorName cursor
    for
    Select placeID from place2
    where placekey = @placekey

    open CursorName
    fetch next from CursorName into @placeID

    --do your stuff

    deallocate CursorName

    --@placeID would have the value from the Select statement

    hope this helps

  4. #4
    Join Date
    Oct 2003
    Posts
    12
    Assuming PlaceID is a varchar(10):

    CREATE @placeID varchar(10)

    Select @placeID = placeID from place2
    where placekey = @placekey
    execute sp_myProcedure @IDnr, @placeID, @placekey


    Hope this helps...

  5. #5
    Join Date
    Sep 2003
    Posts
    16
    Thanks guys!
    Tried the first suggestion, got the error message: Error:170: Line 7:incorrect syntax near '@placeID"

    CREATE PROCEDURE sp_2
    @IDnr int,
    @placekey varchar(100),
    @placeID int

    as
    select placeID INTO @placeID
    from place2
    where placekey = @placekey

    GO

    tried the third option, get the same message..

    CREATE PROCEDURE sp_2
    @IDnr int,
    @placekey varchar(100),
    @placeID int

    As

    CREATE @placeID varchar(10)

    Select @placeID = placeID from place2
    where placekey = @placekey
    execute sp_myProcedure @IDnr, @placeID, @placekey

    GO

    This is my first attempt to write store procedures in MSSQL and I am obviously doing something wrong here..

    Going to try the second suggestion as well, but I guess I then have to know what a cursor is..
    /Helena

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if

    Code:
    Select placeID from place2 where placekey = @placekey
    returns one row then you could use

    Code:
    CREATE PROCEDURE sp_2 
      @IDnr int
    , @placekey varchar(100)
    As
    
    declare @placeid int
    
    Select @placeid = placeID from place2 where placekey = @placekey
    
    execute sp_myProcedure @IDnr, @placeID, @placekey
    
    GO
    I am assuming placeid is an int, if not adjust the declare as needed.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by Helena
    Thanks guys!
    Tried the first suggestion, got the error message: Error:170: Line 7:incorrect syntax near '@placeID"

    CREATE PROCEDURE sp_2
    @IDnr int,
    @placekey varchar(100),
    @placeID int

    as
    select placeID INTO @placeID
    from place2
    where placekey = @placekey

    GO

    tried the third option, get the same message..

    CREATE PROCEDURE sp_2
    @IDnr int,
    @placekey varchar(100),
    @placeID int

    As

    CREATE @placeID varchar(10)

    Select @placeID = placeID from place2
    where placekey = @placekey
    execute sp_myProcedure @IDnr, @placeID, @placekey

    GO

    This is my first attempt to write store procedures in MSSQL and I am obviously doing something wrong here..

    Going to try the second suggestion as well, but I guess I then have to know what a cursor is..
    /Helena
    Paul Young is totally right ! SELECT INTO is Oracle syntax, not MSSQL !

    Sorry about that.

    CVM

Posting Permissions

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