Need to dynamically check for a record and insert if not exist

Question

I wanted to write as script that checks if a record exists in a table, and insert if it doesn't exist. I want to run it in different databases dynamically.

SELECT 1 from 
(SELECT parameter as parameter from @db_name.dbo.Table) a
WHERE parameter = 'new record'
if it doesn't retrieve a value
BEGIN 
INSERT

I was trying to use a dynamic sql, but I stuck. Any guidance is appreciated.

Answer 1

you can check with if exists

IF NOT EXISTS (select 1 from dbo.Table WHERE parameter = 'new record')
  BEGIN
    -- INSERT
  End 
ELSE 
  BEGIN
    -- UPDATE
  END

but if you want to do it in dynamic sql :

declare @dbname varchar(100) 
        ,@sqlstring nvarchar(4000)
-- to avoid SQL injection you can do :
IF ( SELECT OBJECT_ID(@dbname+'.dbo.Table')) is not null 
   BEGIN
set @sqlstring = 'IF NOT EXISTS (select 1 from'+@dbname+'.dbo.Table WHERE parameter = ''new record'')
  BEGIN
    -- INSERT
  End 
ELSE 
  BEGIN
    -- UPDATE
  END'
END
EXEC sp_executesql @sqlstring