Using Dynamic in Clause in Mssql

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 dynamic IN clause in MSSQL

You can if you want do some dynamic SQL but I think it is not really competitive..

  DECLARE   @Status nVARCHAR(400),
@SQL nvarchar(500)

SET @status = '''Closed'''+','+'''OPEN'''
set @SQL = '
select * from [MYTABLE] where status in('+@status +')'

exec sp_executesql @SQL
GO

Dynamic IN clause with condition

There's no need for dynamic SQL here at all; in fact your use of it here is dangerous. You are leaving yourself wide open to SQL injection attacks. I strongly suggest you research the dangers of such a fatal vulnerability. SQLK Injection is a problem that has existed for decades and there is no excuse to writing such code any more; ignorance is not an excuse when so many companies have been named and shamed over the last 15 years for such badly written code resulting in huge numbers of data breaches.

Anyway, onto the answer. One method would be to use a table type parameter. I define a table variable here, but if this is a call from an application you'll need to look into creating and using a TVP:

SELECT ED.Name,
ED.Age,
ED.Phone,
EA.Address
FROM dbo.EmployeeDetails ED
JOIN dbo.EmployeeAddress EA ON EA.EmpId = ED.EmpID
WHERE ED.Name LIKE '%' + @Name + '%'
AND (EA.Status IN (SELECT Status FROM @Statuses)
OR NOT EXISTS (SELECT 1 FROM @Statuses)
ORDER BY ED.CreatedDate DESC
OFFSET @PageNum * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
OPTION (RECOMPILE);

Alternertively, if you are passing a delimited string for your list (which it seem you are) you could use a string splitter and split the value. This assumes you are using a fully supported version of SQL Server (if not, look into an inline user defined table value string splitter) and the value is comma (,) delimited.

SELECT ED.Name,
ED.Age,
ED.Phone,
EA.Address
FROM dbo.EmployeeDetails ED
JOIN dbo.EmployeeAddress EA ON EA.EmpId = ED.EmpID
WHERE ED.Name LIKE '%' + @Name + '%'
AND (EA.Status IN (SELECT SS.[value] FROM STRING_SPLIT(@Status,',') SS)
OR @Status IS NULL)
ORDER BY ED.CreatedDate DESC
OFFSET @PageNum * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
OPTION (RECOMPILE);

How to use IN operator in SQL for a dynamically generated string?

You need to execute it as dynamic sql:

Set @sql='SELECT ... from ...  WHERE ... NOT IN ('+@days+')'

exec sp_executesql @sql

If not, the whole param value will be considered as one value.

How to EXECUTE dynamic query in WITH clause in SQL Server

You can't use EXECUTE inside a CTE.

To do it using the approach you are trying, you'd need to encapsulate the whole query into a dynamic sql statement that builds up the CTE (WITH statement) dynamically, appending @V_QUERY to that dynamic sql statement.

e.g. something like

DECLARE @SQL NVARCHAR(MAX)
SET @SQL =
'WITH Results AS (' + @V_QUERY + ')
SELECT *
FROM Results
WHERE [row_num] BETWEEN (((@IN_PAGEINDEX - 1) * @IN_PAGESIZE) + 1) AND (@IN_PAGEINDEX * @IN_PAGESIZE)'

EXECUTE sp_executesql @SQL, N'@IN_PAGEINDEX INT, @IN_PAGESIZE INT', @IN_PAGEINDEX, @IN_PAGESIZE

Obligatory sidenote - wherever using dynamic sql, make sure you guard against SQL injection risks!

Can we do dynamic SQL in From Clause in a Select Query?

In order to use dynamic table in from clause , you must run command EXEC(@sql) outside :
In your above example :

DECLARE @sql NVARCHAR(MAX)

DECLARE @dynamicSql NVARCHAR(MAX)

SET @sql='SELECT * table'

SET @dynamicSql ='SELECT t.*, a+b AS total_sum
FROM
(
'+@sql+'
) t'

EXECUTE sp_executesql @dynamicSql

SQL - Dynamic Condition in Where Clause

I fixed the syntax issues in your last updated. This should work...

DECLARE @where nvarchar(50) = ' and hcc_18 = 1'
,@sql nvarchar(MAX) ,
@CareProviderId int=null,
@Patient nvarchar(60)=null,
@LocationId int=null

set @sql = 'select *
FROM vw_patient_attributes t1
INNER JOIN STG_OSHODS_DW.osh_rpt.dim_member_care_measures t2
ON t1.PatientID = t2.emr_id
WHERE t1.PreferredServiceLocationID = case when '+ convert(varchar(8),isnull(@LocationId,0)) +' = 0 then t1.PreferredServiceLocationID else ' + convert(varchar,isnull(@LocationId,0)) + ' end
AND (t1.CareProviderID = case when ' + convert(varchar,isnull(@CareProviderId,0)) + ' = 0 then t1.CareProviderID else ' + convert(varchar,isnull(@CareProviderId,0)) + ' end
AND (t1.FullName like ''%' + isnull(@Patient,'') + '%'' OR ' + isnull(@Patient,0) + '=0)' + @where

print(@sql)
--exec(@sql)

Dynamic query 'in clause' parameter not working

Try this:

    alter PROCEDURE [dbo].[Test_In_Clause]

-- Add the parameters for the stored procedure here
@name nvarchar(50) = NULL,
@class nvarchar(50) = NULL

AS
BEGIN

declare
@sql nvarchar(max),
@ParameterDef NVARCHAR(500)

set @ParameterDef = N'@name nvarchar(50),
@class nvarchar(50)'

set @sql = 'Select * from aaa_Students where Name = @name and Class in (@class)'
print @sql
execute sp_Executesql @sql, N'@name nvarchar(50),@class nvarchar(50)', @name = @name, @class = @class


Related Topics



Leave a reply



Submit