[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

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,
0
midavis
Asked:
midavis
  • 2
  • 2
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now