EXECUTE sp_executesql returning last row instead of first

I have this statement in a stored procedure below.  When I run it everytihng works ok.  If I try to do it like the second example I am getting the last row instead of the first row.  I am trying to change it to the second way so I can pull out more than 1 piece of data.

Working....
set @SQLString = N'set @id= (select top 1 t1.id from table1 t1)'

      set @ParmDefinition = N'@id int OUTPUT'
      EXECUTE sp_executesql
      @SQLString,
      @ParmDefinition,
      @id = @prospectid OUTPUT,


Returns last row instead of first
set @SQLString = N'select @id= t1.id from table1 t1'

      set @ParmDefinition = N'@id int OUTPUT'
      EXECUTE sp_executesql
      @SQLString,
      @ParmDefinition,
      @id = @prospectid OUTPUT,
midavisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

izblankCommented:
It's not the stored procedure, it's just the way variable assignment in SELECT works
0
midavisAuthor Commented:
then how do I only get the first value?  Right now it goes through every row obtained then just uses the last one.
0
izblankCommented:
Yow can use SET ROWCOUNT 1 (just do not forget to unset it )
0
midavisAuthor Commented:
no, cant do that.  This proc runs quite a bit and dont want to be caught.  Thanks for the help. I am going another route
0
Scott PletcherSenior DBACommented:
You can return multiple values from sp_executeSQL, so try something like this, for exampe:


set @SQLString = N'select top 1 @id = id, @col2 = col2 from table1 t1)'

     set @ParmDefinition = N'@id int OUTPUT, @col2 varchar(30) OUTPUT'
     EXECUTE sp_executesql
     @SQLString,
     @ParmDefinition,
     @id = @prospectid OUTPUT, @col2 = @col2 OUTPUT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.