Sql Server Pass Column Name as Where Clause Parameter

SQL Server Pass column name as where clause parameter

Here is one more way to do that, but it depends on local settings on the server, so you want to use Set DateFirst to 7:

SET DATEFIRST 7;

SELECT RP.Saturday,
RP.Sunday,
RP.Monday,
RP.Tuesday,
RP.Wednesday,
RP.Thursday,
RP.Friday,
RP.SalesMan,
MC.CustomerID,
MC.CustomerName
FROM RoutePlan RP
FULL JOIN MasterCustomer MC ON RP.CustomerID = MC.CustomerID
WHERE MC.CreatedBy = 'abc'
AND (ISNULL(RP.Saturday, 0) * 1) +
(ISNULL(RP.Sunday, 0) * 2) +
(ISNULL(RP.Monday, 0) * 3) +
(ISNULL(RP.Tuesday, 0) * 4) +
(ISNULL(RP.Wednesday, 0) * 5) +
(ISNULL(RP.Thursday, 0) * 6) +
(ISNULL(RP.Friday, 0) * 7) = DATEPART((weekday,GETDATE());

parameter as column name in where clause

You can use dynamic SQL:

Declare @SQL VARCHAR(4000)
SET @SQL = 'SELECT * FROM BORVALIDATION WHERE ' + @COLUMNNAME + '= ''BBNAME''';
EXEC(@SQL);

How to pass column name as variable in dynamic query in SQL Server?

This should be what you are after. There's a few comments in the SQL you need to pay attention to:

DECLARE @SQL nvarchar(max),
@tname sysname, --note the datatype change
@cname sysname, --note the datatype change
@acc_num nvarchar(50),
@dp_code nvarchar(100); --This is never used in your sample query
SET @cname = 'column_name';
SET @acc_num = 'xyz';
SET @tname = 'table_name';


SET @SQL = N'SELECT ' + NCHAR(13) + NCHAR(10) +
STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' ' + N'COUNT DISTINCT(' + QUOTENAME(C.Column_Name) + N') AS ' + QUOTENAME(C.Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.Table_Name = @tname
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(N'')),1,10,N'') + NCHAR(13) + NCHAR(10) +
N'FROM ' + QUOTENAME(@tname) + NCHAR(13) + NCHAR(10) +
N'WHERE ' + QUOTENAME(@cname) + N' = @Acc_Num;'

PRINT @SQL; --YOur debugging best friend
EXEC sp_executesql @SQL, N'@Acc_Num varchar(100)',@Acc_Num = @acc_num;

This parametrised the query instead, using sp_executesql, and nicely formats it for your PRINT statement.

If you don't understand then please do ask.

How to pass a string of column name as a parameter into a CREATE TABLE FUNCTION in BigQuery

How to pass column names in a SQL query as parameters using report builder 3.0

It's hard to tell what the problem is without seeing your code but you can't just swap a string in the SQL like (I think) you are trying to do.

To test your SQL just do something simple in Management Studio like

DECLARE @MyParm varchar(256) = 'myColumnName'
SELECT *, @MyParm FROM myTable

This will return all columns from MyTable, plus a string value of 'myColumnName' as the final column.

If this is what you are trying to do then you'll probably need to change your SQL to use dynamic SQL, something like this.

DECLARE @MyParm varchar(256) = 'myCOlumnName'
DECLARE @SQL varchar(max)

SET @SQL = 'SELECT *, ' + @MyParm + ' FROM myTable'

EXEC (@SQL)

If I've got this completely wrong, please post some sample code so we can see what you are attempting.

EDIT: here is a specific answer based on your sample code

DECLARE @Column sysname = 'Column999'
DECLARE @SQL varchar(max)

SET @SQL =
'SELECT
table_name.column1 ,table_name.column21 ,table_name.column15 ,table_name.column6
,table_name.column9 ,table_name.column2 ,table_name.column19
,' + @column +
' FROM table_name
WHERE table_name.company LIKE @company
AND table_name.platform LIKE @platform
AND (' + @column + ' is null OR ' + @column + ' = '''' OR ' + @column + ' = ''N/A'' OR ' + @column + ' = ''Unknown'')'

-- uncomment below to check SQL statement is correct
--print @sql

-- exec the SQL statement
exec (@sql)

As it stands, this will execute the following sql

SELECT 
table_name.column1 ,table_name.column21 ,table_name.column15 ,table_name.column6
,table_name.column9 ,table_name.column2 ,table_name.column19
,Column999 FROM table_name
WHERE table_name.company LIKE @company
AND table_name.platform LIKE @platform
AND (Column999 is null OR Column999 = '' OR Column999 = 'N/A' OR Column999 = 'Unknown')


Related Topics



Leave a reply



Submit