Need to get results of an EXEC(@string_variable) into a local variable

• Takauma

  Say Hey Kid

  Points: 697

  More actions

  September 12, 2007 at 11:32 am

  #180830

  Here's one example:

  declare @LockCount int, @WhereClause varchar(1000)

  set @WhereClause = ' Projects.OwnerCode = 25 '

  execute(' select @LockCount = count(*) from DBO.ProjectChangeID join Projects on Projects.ProjectID = ProjectChangeID.ProjectID join OwnerInformation on Projects.OwnerCode = OwnerInformation.OwnerCode where ProjectChangeID.LockFlag > 0 and ' + @WhereClause)

  Resulting Error:  Must declare the scalar variable "@LockCount".

  I have tried variations on this theme, to no avail, (mostly syntax errors.)  There's gotta be a way to do this.  Suggestions?  Thanks in advance,  Joel

  Takauma

• noeld

  SSC Guru

  Points: 96590

  More actions

  September 12, 2007 at 11:43 am

  #733223

  have a look at *sp_executesql* on BOL.

  
  * Noel

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  September 12, 2007 at 1:32 pm

  #733263

  sp_executesql With Example

   

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=395213#bm395219