Sql "If Exists..." Dynamic Query

SQL if exists... dynamic query

Try Executing the Dynamic query and use @@RowCount to find the existence of rows.

DECLARE @Query  NVARCHAR(1000) = 'SELECT * FROM [dbo].[Mytable]',
        @rowcnt INT

EXEC Sp_executesql @query

SELECT @rowcnt = @@ROWCOUNT

IF @rowcnt > 0
  BEGIN
      PRINT 'row present'
  END 

How to execute sub query in if exists condition?

DECLARE @qry VARCHAR(100);
DECLARE @cnt INT;
set @qry = ' where '
DECLARE @ExeQuery VARCHAR(MAX);
SET @ExeQuery='if exists( select * from  ARTICLE_MANAGE '+@qry+' article_id=65)
BEGIN
select top 1* from  ARTICLE_MANAGE order by article_id desc
END
ELSE
BEGIN
select * from  ARTICLE_MANAGE order by article_id desc
END'
 EXEC(@ExeQuery)

If dynamic table exists in the database Drop the table

Simply use like this. It works for me.

-- Create the table for testing
CREATE TABLE Test201711
(
    id INT
)

SELECT NAME
FROM   sys.tables AS t
WHERE  t.name = 'Test201711'

DECLARE @TableName VARCHAR(100) = 'Test' + CONVERT(VARCHAR(6), GETDATE(), 112)

IF OBJECT_ID(@TableName) IS NOT NULL
    EXEC ('DROP Table ' + @TableName)

SELECT NAME
FROM   sys.tables AS t
WHERE  t.name = 'Test201711'

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

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

Checking if a table exists dynamically

You should concat DB name + '.' + Schema name + '.' + Table name, then execute the IF OBJECT_ID(@YourString) IS NOT NULL and put a BEGIN END afterwards... everything which has to be performed if the table exists goes between this BEGIN AND END...

SQL dynamic query: check whether table column has any data

You are not executing the test. You need something like:

declare @rowcheck int = 0;
declare @rowchecksql nvarchar(1000) = N'SELECT @rows=1 WHERE EXISTS (SELECT ['+@fieldName1+'] FROM ['+@dynamicTableName+'] WHERE ['+@fieldName1+'] IS NOT NULL);';
exec sp_executesql @rowchecksql, N'@rows INT OUTPUT', @rows=@rowcheck OUTPUT;

Then you can continue with:

IF @rowcheck = 0
    BEGIN
        ...;
    END

Note that we have to create an OUTPUT parameter to the dynamic sql and then assign this parameter to a variable that we have already declared. Then when we execute the dynamic sql, the variable is assigned the value, and we can then examine it. Note also that COUNT is better here than MAX.

EDIT: Thanks to @Charlieface for pointing out that EXISTS is better still (as the table search will stop as soon as a single record is found meeting the condition).

If table exists with dynamic query in SQL Server

Just do this simple check. No need to query sys.objects

...    
IF OBJECT_ID(@TABLENAME, 'U') IS NULL
BEGIN

Your check failed because you were actually looking for a table called "+ @TABLENAME +"

Dynamic SQL for checking stored procedure existence

You have a couple of problems here. Firstly you had SET Exists = 1, which should be SET @Exists = 1, but you also were referring to an invalid object. You'll need to use sys.procedures and sys.schemas here, and you can't use OBJECT_ID as that'll be run in the context of the database you're connected to. This gives you the following (also tidied up the dynamic SQL):

DECLARE @SQL nvarchar(MAX);

DECLARE @TargetServer sysname, @TargetDB sysname, @TargetSchema sysname, @TransformProcName sysname;

SET @TargetDB = N'Sandbox';
SET @TargetSchema = N'dbo';
SET @TransformProcName = N'sample_sp';

DECLARE @Exists bit;

SET @Sql = N'IF NOT EXISTS (SELECT *' + NCHAR(13) + NCHAR(10) +
           N'               FROM ' + QUOTENAME(@TargetServer) + N'.' + QUOTENAME(@TargetDB) + '.sys.procedures p' + NCHAR(13) + NCHAR(10) +
           N'                    JOIN sys.schemas s ON p.schema_id = s.schema_id' + NCHAR(13) + NCHAR(10) +
           N'               WHERE p.[name] = @TransformProcName' + NCHAR(13) + NCHAR(10) +
           N'                 AND s.[name] = @TargetSchema)' + NCHAR(13) + NCHAR(10) +
           N'       SET @Exists = 0' + NCHAR(13) + NCHAR(10) +
           N'ELSE' + NCHAR(13) + NCHAR(10) +
           N'       SET @Exists = 1;'

PRINT @SQl;
EXEC sp_executesql @Sql,N'@TransformProcName sysname, @TargetSchema sysname, @Exists bit OUTPUT', @TransformProcName = @TransformProcName, @TargetSchema = @TargetSchema, @Exists =  @Exists OUTPUT;
PRINT @Exists;

---

Related Topics