If exists table dynamic sql from variable database id

• mnr123

  SSC-Addicted

  Points: 427

  More actions

  July 16, 2017 at 1:17 pm

  #322640

  Hi 

  I want to write if the table exists in the database in dynamic sql then select from that table .

  I have written this but i want to use in if then

  DECLARE @sql NVARCHAR(MAX);
  set @sql = 'SELECT * FROM ['+ @DBID+'].sys.objects where NAME LIKE ''%DDSC_MappedDimension%'' '

  SET @RunTimeQuery = 'SELECT distinct MAPPEDDIMENSIONCODE,DIMENSION_TYPE FROM ['+ @DBID+'].dbo.DDSC_MappedDimension WHERE MAPPEDDIMENSIONCODE = ''' + @code + '''' + 'and DIMENSION_TYPE = ''' + @Dimension_Type +''''

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  July 16, 2017 at 2:03 pm

  #1951063

  mnr123 - Sunday, July 16, 2017 1:17 PM

  Hi 

  I want to write if the table exists in the database in dynamic sql then select from that table .

  I have written this but i want to use in if then

  DECLARE @sql NVARCHAR(MAX);
  set @sql = 'SELECT * FROM ['+ @DBID+'].sys.objects where NAME LIKE ''%DDSC_MappedDimension%'' '

  SET @RunTimeQuery = 'SELECT distinct MAPPEDDIMENSIONCODE,DIMENSION_TYPE FROM ['+ @DBID+'].dbo.DDSC_MappedDimension WHERE MAPPEDDIMENSIONCODE = ''' + @code + '''' + 'and DIMENSION_TYPE = ''' + @Dimension_Type +''''

  Your logic sounds pretty straightforward.

  DECLARE @Name varchar(128) = 'dbo.Test';
DECLARE @sql Varchar(200);

  IF OBJECT_ID(@Name, 'U') IS NOT NULL
BEGIN
    --TODO: define your query here
    EXECUTE (@SQL);
END;


  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions