Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    26

    Unanswered: Get result from EXEC()

    I currently do this:

    INSERT INTO #tbl2 EXEC(@sql)
    IF @@ROWCOUNT = 1
    INSERT INTO @tbl3 SELECT userID FROM #tbl2
    DELETE FROM #tbl2

    Is there any other way to recieve the result from column userid from the @sql-query?

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Get result from EXEC()

    Hi,

    try to use sp_executesql (see BOL):

    sp_executesql [@stmt =] stmt
    [
    {, [@params =] N'@parameter_name data_type [,...n]' }
    {, [@param1 =] 'value1' [,...n] }
    ]


    Markus

  3. #3
    Join Date
    Jan 2003
    Posts
    26
    How do you mean?

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Hi,

    i don't know what you really want to query, but here an (quick and dirty) example:

    Code:
    -- testing enviroment
    create table usernames( userid int, username varchar(100)  )
    insert into usernames (userid,username) values ( 123, 'moby' )
    insert into usernames (userid,username) values ( 986, 'lars'  )
    -- select * from usernames
    --
    -- vars
    declare @Stmt nvarchar(200), @UserID int, @UserName varchar(100)
    -- SQL-Stattement to get result from
    select @Stmt='select @P1= userid from usernames where username=@P2'
    -- "input" parameter
    select @Username='moby'
    -- query result
    exec sp_executesql @Stmt, N'@P1 int output, @P2 varchar(100)', @P1=@UserID output, @P2=@Username
    -- show result
    print @Username
    print @UserID
    
    -- get rid of testdata
    drop table usernames

  5. #5
    Join Date
    Jan 2003
    Posts
    26

    Thumbs up

    Thank you!

Posting Permissions

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