Query not returning results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query not returning results

Author

Topic

fphoenix
Starting Member

1 Post

Posted - 2015-04-10 : 11:51:12

I'm having an issue using a bit column as a parameter in the where clause of a query. When I use the column as a parameter in the where clause, no results are returned. Can someone help me understand why the query below doesn't work? Will I have to use dynamic SQL in order for the condition in the where clause to return results?


	CREATE TABLE [dbo].[EmployeeTeams](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Emp_Id] [bigint] NULL,
	[A] [bit] NULL,
	[B] [bit] NULL,
	[C] [bit] NULL,
	[D] [bit] NULL,
	[E] [bit] NULL,
	[F] [bit] NULL)

DECLARE @Team VARCHAR(1) = NULL

SET @Team = 'A'

SELECT *
FROM Employee e
LEFT OUTER JOIN Employeeteams t ON e.Emp_id = t.Emp_Id
WHERE (
		@Team IS NULL
		OR ('t.' + Quotename(@Team)) = '1'
		)

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 12:37:15

That won't work. 't.' + Quotename(@Team)) will not be evaluated as a column. you would need to dynamic sql for something like this.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 12:46:19

You could do some stuff with bitwise:

This example uses a Cartesian and is only for quick demo purposes, but you might be able to use the principles to accomplish what you want or you could use dynamic sql .

CREATE TABLE #EmployeeTeams(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Emp_Id] [bigint] NULL,
[Team] int
)

INSERT INTO #EmployeeTeams
VALUES(1,2),(2,4),(3,2),(4,16)

CREATE TABLE #Teams
(
Team Int , TeamName Char(1)
)

INSERT INTO #Teams
VALUES (2,'A'), (4,'B'), (8,'C'), (16,'D'), (32,'E'), (64,'F')

SELECT *
FROM #EmployeeTeams T1, #Teams T2
WHERE (T1.Team & T2.Team = T2.Team)
AND T2.TeamName = 'A'

Kristen
Test

22859 Posts

Posted - 2015-04-12 : 07:42:04

Alternatively:


WHERE (
		@Team IS NULL
		OR (@Team = 'A' AND [A] = 1)
		OR (@Team = 'B' AND [B] = 1)
		...
		)

Probably won't perform very well if the data table is large!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources