July 7, 2016 at 6:21 am
[font="Arial"]Hi,
I am trying to pass values dynamically to IN clause.
I have copied records into variable @String and I am trying to pass this variable as a filter to next query.
Please help me here.
I have attached SQL Query as well.[/font]
[font="System"]CREATE TABLE #Role
(
RoleIDINT IDENTITY(1001,1),
RoleNameNVARCHAR(50),
DisplayNameNVARCHAR(100),
RoleDescriptionNVARCHAR(100),
Constraint pk_Role_RoleID Primary Key (RoleID)
)
INSERT INTO #Role(RoleName, DisplayName, RoleDescription) VALUES('Developer', 'Developer', 'Development Work');
INSERT INTO #Role(RoleName, DisplayName, RoleDescription) VALUES('Tester', 'Tester', 'Testing Work');
INSERT INTO #Role(RoleName, DisplayName, RoleDescription) VALUES('ProjectManager', 'Project Manager', 'Project Management Work');
INSERT INTO #Role(RoleName, DisplayName, RoleDescription) VALUES('DeliveryManager', 'Delivery Manager', 'Project Management and Delivery Work');
CREATE TABLE #User
(
UserIDINT IDENTITY(110001,1),
UserNameNVARCHAR(50),
RoleIDINT,
Constraint pk_User_UserID Primary Key(UserID),
--Constraint fk_User_RoleID Foreign Key(RoleID) References #Role(RoleID)
)
INSERT INTO #User(UserName, RoleID) VALUES('Roger W', 1003);
INSERT INTO #User(UserName, RoleID) VALUES('Mike S', 1002);
INSERT INTO #User(UserName, RoleID) VALUES('Venus F',1001);
INSERT INTO #User(UserName, RoleID) VALUES('Frank P', 1001);
INSERT INTO #User(UserName, RoleID) VALUES('Kevin R', 1001);
INSERT INTO #User(UserName, RoleID) VALUES('Linda G', 1001);
INSERT INTO #User(UserName, RoleID) VALUES('Will A', 1004);
INSERT INTO #User(UserName, RoleID) VALUES('Adam J', 1001);
INSERT INTO #User(UserName, RoleID) VALUES('Jerom L', 1001);
INSERT INTO #User(UserName, RoleID) VALUES('Raymond S', 1003);
INSERT INTO #User(UserName, RoleID) VALUES('Andy M', 1002);
INSERT INTO #User(UserName, RoleID) VALUES('Jenny W', 1001);
INSERT INTO #User(UserName, RoleID) VALUES('John A', 1002);
INSERT INTO #User(UserName, RoleID) VALUES('Chris B', 1001);
INSERT INTO #User(UserName, RoleID) VALUES('Martina D', 1001);
CREATE TABLE #UserTask
(
TaskIDINT IDENTITY(100000001,1),
TaskNameNVARCHAR(100),
TaskDescriptionNVARCHAR(100),
UserIDINT,
StartDateDATE,
EndDateDATE,
StatusNVARCHAR(50),
Constraint pk_UserTask_TaskID Primary Key(TaskID),
--Constraint fk_UserTask_UserID Foreign Key(UserID) References #User(UserID)
)
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New SQL Script', 'Creating new SQL script', 110003, '02/28/2015', '03/12/2015', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New Web Page', 'Adding new web page', 110008, '01/18/2015', '03/05/2015', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('Update SQL Script', 'Modifying new SQL script', 110015, '01/09/2015', '01/15/2015', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New Report Test', 'Testing new Report', 110011, '06/30/2016', '', 'Progressing');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('Add In Test', 'Testing new addin', 110013, '04/15/2016', '04/20/2016', 'Aborted');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New DB Script', 'Creating new PL/SQL script', 110003, '01/19/2016', '02/22/2016', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New Java Script', 'Creating new Java script', 110005, '12/02/2015', '01/29/2016', 'Aborted');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New C# Script', 'Creating new C# script', 110003, '02/28/2015', '03/12/2015', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New SQL Script', 'Creating new SQL script', 110001, '05/10/2016', '', 'Progressing');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New DB Design', 'Designing new database', 110006, '06/14/2016', '', 'Progressing');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('Update web page', 'Modifying web page', 110009, '05/28/2016', '06/27/2016', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New TSQL script', 'Creating new SQL script', 110005, '01/10/2016', '02/12/2016', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('Update SQL Script', 'Modifying SQL script', 110012, '04/16/2016', '', 'Progressing');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New JQuery Script', 'Creating new JQuery script', 110002, '05/12/2016', '05/31/2016', 'Completed');
INSERT INTO #UserTask(TaskName, TaskDescription, UserID, StartDate, EndDate, Status) VALUES('New C# Script', 'Creating new C# script', 110004, '01/24/2016', '03/29/2016', 'Completed');
DECLARE @Role AS NVARCHAR(50) = 'ProjectManager';
DECLARE @String AS VARCHAR(100) = '';
SELECTDistinct @String = CASE
WHEN @Role = 'Developer' THEN 'Developer'
WHEN @Role = 'Tester' THEN 'Tester'
WHEN @Role = 'ProjectManager' THEN (
SELECT Distinct
STUFF(
(SELECT ', ' + ''''+R.RoleName+''''
FROM #Role AS R
WHERE R.RoleID IN (1001, 1002, 1003)
FOR XML PATH('')), 1, 1, '') AS UserRole
FROM#Role WITH (NoLock)
)
END
FROM #Role AS RR WITH (NoLock)
SELECT @String
SELECT DISTINCT UT.TaskID, UT.TaskName, U.UserName, UT.StartDate, UT.EndDate, UT.Status, R.RoleName
FROM [#User] AS U WITH (NoLock)
LEFT JOIN [#UserTask] AS UT WITH (NoLock)
ONU.UserID = UT.UserID
LEFT JOIN[#Role] AS R WITH (NoLock)
ONR.RoleID = U.RoleID
WHERER.RoleName IN(@String)
DROP TABLE #Role
DROP TABLE #User
DROP TABLE #UserTask[/font]
July 7, 2016 at 6:49 am
Your condition is the same as writing WHERE R.RoleName = @String
Either you split the values (the best splitter can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/) and remove the quotes.
SELECT DISTINCT UT.TaskID, UT.TaskName, U.UserName, UT.StartDate, UT.EndDate, UT.Status, R.RoleName
FROM [#User] AS U
LEFT JOIN [#UserTask] AS UT ON U.UserID = UT.UserID
LEFT JOIN[#Role] AS R ON R.RoleID = U.RoleID
JOIN dbo.DelimitedSplit8K(@String, ',') S ON R.RoleName = S.Item;
Or you use dynamic SQL which would be open to SQL Injection in the way you're doing it right now, so you'll need to change it a bit.
Here's an alternate option, you might want to read this article to prevent future problems: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
SELECT DISTINCT UT.TaskID, UT.TaskName, U.UserName, UT.StartDate, UT.EndDate, UT.Status, R.RoleName
FROM [#User] AS U
LEFT JOIN [#UserTask] AS UT ON U.UserID = UT.UserID
LEFT JOIN[#Role] AS R ON R.RoleID = U.RoleID
WHERE R.RoleName = @Role
OR (@Role = 'ProjectManager' AND R.RoleID IN (1001, 1002, 1003));
By the way, using NOLOCK on all tables is a very bad idea:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
July 7, 2016 at 7:29 am
You already have a temp table with the values you want. Why not use that and do:
IN (
SELECT RoleName FROM #Role WHERE @Role = 'ProjectManager'
UNION ALL
SELECT @Role)
Wouldn't that be easier than all the string splitting and splicing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2016 at 9:57 am
If you have a large number of strings to test against, or a dynamic situation, I would populate a table (#rolechoices) and join to it as opposed to trying to dynamically produce an IN clause.
SELECT DISTINCT UT.TaskID, UT.TaskName, U.UserName, UT.StartDate, UT.EndDate, UT.Status, R.RoleName
FROM [#User] AS U
LEFT JOIN [#UserTask] AS UT
ONU.UserID = UT.UserID
LEFT JOIN[#Role] AS R
ONR.RoleID = U.RoleID
LEFT JOIN [#RoleChoices] as RC
on R.RoleName = RC.RoleName
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 8, 2016 at 6:29 am
Thanks Luis.
I will take of that NoLock.
Thanks once again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply