Variable assignment from dynamic query

• Keith Levenson

  SSC Rookie

  Points: 29

  More actions

  March 11, 2005 at 12:20 pm

  #65465

  It seems that you just can't assign a variable to a dynamic query result:

  DECLARE @userid as nvarchar(10),

   @myTable as nvarchar(10),

   @strUser as nvarchar(100)

   SET @myTable = 16707

   SET @strUser = N'SELECT ' + @user-id + ' = UserID from dlr_' + @myTable + '_User where UserLName = ''Kratky'''

   EXEC sp_executesql

   @statement = @strUser

   IF ISNULL(@UserId,0) = 0

    PRINT 'Hi Kids'

  No matter what I try, I get 'variable must be defined', or the variable never gets assigned.  Any ideas?

  Thanks!

  /Keith

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 11, 2005 at 12:27 pm

  #546706

  DECLARE @userid as nvarchar(10),

   @myTable as nvarchar(10),

   @strUser as nvarchar(100)

   SET @myTable = 16707

   SET @strUser = N'SELECT ' + @user-id + ' = UserID from dlr_' + @myTable + '_User where UserLName = ''Kratky'''

   EXEC sp_executesql

   @statement = @strUser, N'@UserID as nvarchar(10)', @userid  OUTPUT

   IF ISNULL(@UserId,0) = 0

    PRINT 'Hi Kids'

  
  * Noel