Unable to make this query run.. trying to build query by concatenating strings

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  August 29, 2006 at 3:15 pm

  #657403

  Try this:

  SET @queryString = 'SELECT ' +  @EmployeeNo + ' = b.employee_no, @AccessLevelID = a.Access_Seq FROM ' +

                                 @dbName + '..employee a, ' + @dbName + '..userlog b WHERE a.employee_no = b.employee_no

                                 AND b.uname = ' + @LoginName

  I wasn't born stupid - I had to study.

• ajashadi

  Ten Centuries

  Points: 1017

  More actions

  August 29, 2006 at 3:19 pm

  #657405

  wont work because @EmployeeNo is int and so is @AccessLevelID.

  Server: Msg 245, Level 16, State 1, Line 17

  Syntax error converting the varchar value 'SELECT ' to a column of data type int.

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  August 29, 2006 at 3:44 pm

  #657406

  try this

  SET @queryString = 'SELECT b.employee_no, a.Access_Seq FROM ' +

                                 rtrim(convert(char,@dbName)) + '..employee a, ' + rtrim(convert(char,@dbName)) + '..userlog b WHERE a.employee_no = b.employee_no

                                 AND b.uname = ''' + @LoginName+''''

  this will work..

  The thing you are doing is.. in dynamic sql if you use local variables, you cant get them after that statement. because scope problem ..the declared variables doesn't exist after that scope

• ajashadi

  Ten Centuries

  Points: 1017

  More actions

  August 30, 2006 at 7:01 am

  #657488

  Yeah but the whole idea is to store the 2 col's returned by SELECT in the variables... i guess this will not work. I should look in the direction of creating temp table and storing values in that select clause... that way I dont have to use those 2 variables..

  I agree ur query will work but will not take care of var's which are needed.

  thanks again.

• Ken McKelvey

  SSCoach

  Points: 18975

  More actions

  August 30, 2006 at 7:26 am

  #657506

  Try sp_executesql with output parameters. Something like:

  declare @EmployeeNo int, @AccessLevelID int, @err int

  set @queryString = 'select @EmployeeNo = b.employee_no, @AccessLevelID = a.Access_Seq from '

    + @dbName + '..employee a, '

   + @dbName + '..userlog b where a.employee_no = b.employee_no and b.uname = @LoginName'

  exec @err = sp_executesql @queryString

    ,N'@EmployeeNo int output, @AccessLevelID int output, @LoginName varchar(50)'

    ,@EmployeeNo output

    ,@AccessLevelID output

    ,@LoginName

  select @EmployeeNo, @AccessLevelID

   

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  August 30, 2006 at 10:38 am

  #657626

  Yes, Using Output paremeters it should work