SQL Server - in Clause With a Declared Variable

SQL Server - In clause with a declared variable

You need to execute this as a dynamic sp like

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3,4,22,6014'
declare @sql nvarchar(Max)

Set @sql='SELECT * FROM [A] WHERE Id NOT IN ('+@ExcludedList+')'

exec sp_executesql @sql

Using variable with SQL WHERE IN statement

Instead of using a string, you could use a table variable

    Declare @NodeId table (NodeId varchar(30))
    insert into @NodeId
    select 'SomeNodeID' union all select 'SomeOtherNodeID'
    
    IF EXISTS (SELECT * FROM dbo.tblNodeMaster WHERE NodeId in (select NodeId from @nodeId))
...

Cant define SQL variable when using WITH statement

Just do

DECLARE @dt DATETIME;

SET @dt = GETDATE();

WITH A
     AS (SELECT TOP 1000 *
         FROM   dbo.SomeTable)
SELECT *
FROM   A;

PRINT DATEDIFF(SS, GETDATE(), @dt);

The only valid thing following a CTE definition is a single statement using it

SQL Server : declare variables inside FROM clause

Because of your pagination logic you need write only one-statements queries. In your case, the query may look like:

SELECT *
FROM
(
    -- your queries
) DS
CROSS APPLY
(
    VALUES (1, 'test 1', GETUTCDATE())
          ,(2, 'test 2', GETUTCDATE())

) Variables ([ID], [ProductName], [Date])
    -- then some logic here like
WHERE DS.[ID] = Variables.[ID]

Then, your pagination query will wrapped this.

It's not efficient. For pagination you can use ORDER BY and OFFSET- FETCH.

Should Getdate() in WITH clause be declared as variable?

I ran both approaches in SQL Server 2016. I did not find any difference. But, as @Cato mentioned in the comments, it is better to declare as a variable and pass the variable, instead of waiting for the optimizer to take care of it.

Approach 1

DECLARE @currentDate DATE = GETDATE()
;WITH CalendarSequence as(
  SELECT @currentDate as RollingDate
  UNION ALL
  SELECT DateAdd(month, -1, RollingDate) as RollingDate
  FROM CalendarSequence
  WHERE DateAdd(month, -1, RollingDate) > Convert(date, '2018-01-01')
)
SELECT * FROM CalendarSequence

Approach 2

;WITH CalendarSequence as(
  SELECT Getdate() as RollingDate
  UNION ALL
  SELECT DateAdd(month, -1, RollingDate) as RollingDate
  FROM CalendarSequence
  WHERE DateAdd(month, -1, RollingDate) > Convert(date, '2018-01-01')
)
SELECT * FROM CalendarSequence

Both of them are having the same execution plan. No changes at all.

Declare a variable with SELECT SQL statement to be used in the WHERE clause

You can use APPLY to create something like a named variable row-wise. If the value is calculated once and is valid for the whole set (more like a constant), you might use a CTE (starting with WITH before the SELECT).

The following will use an APPLY to find the table's name for all columns. You can use The.ImportantVariable in the column list as well as in a WHERE-clause:

SELECT The.ImportantVariable
      ,c.*
FROM sys.columns AS c
OUTER APPLY(SELECT name FROM sys.objects AS o WHERE c.object_id=o.object_id) AS The(ImportantVariable)
WHERE The.ImportantVariable LIKE 'a%';

UPDATE Compare CTE and APPLY

Check this! There are some constant values in 1 single row provided by the CTE which you can CROSS JOIN into your query and use as named constants

WITH SomeConstants AS
(
    SELECT 'I''m a constant value' AS Constant1 --might be a complex statement too!
           ,0 AS Constant2
)
SELECT The.ImportantVariable
      ,Constant1
      ,Constant2
      ,c.*
FROM SomeConstants
CROSS JOIN sys.columns AS c
OUTER APPLY(SELECT name FROM sys.objects AS o WHERE c.object_id=o.object_id) AS The(ImportantVariable)
WHERE The.ImportantVariable LIKE 'a%'

SQL Server conditional where clause based on declared variable

You are looking for this

DECLARE @ITEST INT = 1

SELECT NAME, LNAME, CADDRESS
FROM JEEVEN
WHERE (@ITEST = 1 AND EVEN_KEY > 5 AND EVEN_KEY < 10)
   OR (@ITEST = 2 AND EVEN_KEY > 20 AND EVEN_KEY < 30)

---

Related Topics