Must declare the scalar variable when assigning SELECT statement to nvarchar variable

• chilling

  Grasshopper

  Points: 11

  More actions

  March 24, 2010 at 12:18 pm

  #222335

  Being an adhoc coder I am trying to write a AP and getting the following error

  Msg 137, Level 15, State 2, Line 2

  Must declare the scalar variable "@SageDataBase".

  with this:

  DECLARE @sql nvarchar(2000)

  DECLARE @SageDataBase nvarchar(60)

  SET @sql = N'

  @SageDataBase =

  SELECT ACSODBC from tblCompany

  WHERE CompanyID = 1016'

  EXEC(@SQL)

  The Select statement should only return one value as CompanyID is the table index, in the real thing I am passing 1016 as a paramater.

  I want to go on and use @SageDataBase in another SELECT statement .... within the next select statement I can not create a derived table as @SageDataBase is the database in am looking the data up in..

• Paul White

  SSC Guru

  Points: 150468

  More actions

  March 24, 2010 at 1:04 pm

  #1139189

  DECLARE @SageDataBase NVARCHAR(60);

  SET @SageDataBase =

  (

  SELECT ACSODBC

  FROM tblCompany

  WHERE CompanyID = 1016

  );

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• ssismaddy

  SSCarpal Tunnel

  Points: 4850

  More actions

  March 24, 2010 at 1:57 pm

  #1139214

  DECLARE @SageDataBase NVARCHAR(60);

  SELECT @SageDataBase = ACSODBC

  FROM tblCompany

  WHERE CompanyID = 1016