Loop Through Table Value in Where Claus

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 2008 Forums

Transact-SQL (2008)

Loop Through Table Value in Where Claus

Author

Topic

mcujardo
Starting Member

5 Posts

Posted - 2012-01-11 : 20:24:23

Good Day/Evening To All.

I need your input on what I have done wrong or what is wrong with the stored procedure that I have below.

My goal is to make this stored procedure accept multiple values for @manufacturingLines. My question is that why does the stored procedure below only returns the results of the first item value on @manufacturingLines.

Example:

if I pass the following values 'A, B, C' to the @manufacturingLines variable, the stored procedures only returns the results for A and not for B and C.

ALTER PROCEDURE [dbo].[sp_weekly_utilization_oee]
(@startDate AS DATETIME, @endDate AS DATETIME, @manufacturingLine AS NVARCHAR(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- set first day of the week to Thursday
SET DATEFIRST 4;

SELECT [site].site_name
, util_state.state_desc
, COUNT(state_desc) AS [Count]
, SUM(duration) AS TotalDuration
FROM
(SELECT cust_util_log.event_time_local
, cust_util_log.state_cd
, cust_util_log.reas_cd
, cust_util_log.duration
, cust_util_log.raw_reas_cd
, cust_util_log.ent_id
FROM cust_util_log
UNION
SELECT util_log.event_time_local
, util_log.state_cd
, util_log.reas_cd
, util_log.duration
, util_log.raw_reas_cd
, util_log.ent_id
FROM util_log) cust_util
JOIN util_state ON cust_util.state_cd = util_state.state_cd
JOIN [site] ON cust_util.ent_id = [site].ent_id
WHERE ([site].site_name IN (SELECT * FROM dbo.fn_String_To_Table(@manufacturingLine,',',1))
AND ((event_time_local BETWEEN @startDate AND @endDate)
AND (cust_util.state_cd= 5 OR cust_util.state_cd= 6 OR cust_util.state_cd= 7)))
GROUP BY state_desc,site_name

Thanks you in advance for your inputs and suggestions

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 20:48:31

can you just run this statement and see is it returning 3 records ?


declare @manufacturingLine NVARCHAR(10) = 'A, B, C'
SELECT * FROM dbo.fn_String_To_Table(@manufacturingLine,',',1)

KH
[spoiler]Time is always against us[/spoiler]

mcujardo
Starting Member

5 Posts

Posted - 2012-01-11 : 21:32:16

khtan,

declare @manufacturingLine NVARCHAR(10) = 'A, B, C'
SELECT * FROM dbo.fn_String_To_Table(@manufacturingLine,',',1)

returns

A
B
C

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 21:43:51

then the problem is not the @manufacturingLines, it might be other condition that filter out record for B & C.

Verify the data and break out the query and execute it part by part to identify the problem

KH
[spoiler]Time is always against us[/spoiler]

mcujardo
Starting Member

5 Posts

Posted - 2012-01-11 : 22:18:10

Khatan,

I got it to work now. The problem was @manufacturingLines is declared as NVARCHAR(10) which is actually too small to accept multiple values.
Increased its size to 100 and the problem is solved.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 22:48:00

i see. Thanks for posting back.

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-12 : 05:33:25

maybe you should consider modifying the stored proc to accept a table valued type rather than a comma separated string.

Information can be found here.
http://www.sommarskog.se/arrays-in-sql-2008.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources