October 16, 2010 at 4:35 am
Hi
I need your precious commements on the following issue.
We need to display customer information in 3 indipendent tables in the same page of UI(User Interface). The input parameter to the Stored procedure are @policyNumber or @callerName or @contactnumber.
Ideally, we need to write three Stored Procedures to handle the above situation.
But after analyzing, I found that we are calling the same procedure 3 times with three different input parameter.
Now, I want to call a single procedure instead of three where all the three parameters are handled.
I thought to use temporary table to handle so. My Query is below
For @callerName
select csext_id1 as CALLERNAME,csext_id2 as CONTACTNUMBER,csext_id12 as POLICYNUMBER,FROM tblCases cs,tblCaseCustomAttributes ca,tblProcess pr , tblCaseHistory cshy,
dbo.tblteam tm , tblProcess subpr WHERE cs.cs_cid = ca.csext_cs_cid AND cs.cs_cid *= cshy.cshy_cs_cid AND cs.cs_pr_pid = pr.pr_pid
AND tm.tm_tid = pr.pr_tm_tid AND cs.cs_sub_pr_pid *= subpr.pr_pid AND csext_id1 IN (''' + REPLACE (@callerName , ',' , CHAR(39)+','+CHAR(39) ) + ''')
For @policyNumber
Same Query but
csext_id1 IN (''' + REPLACE (@callerName , ',' , CHAR(39)+','+CHAR(39) ) + ''')
replaced by
csext_id2 IN (''' + REPLACE @policyNumber, ',' , CHAR(39)+','+CHAR(39) ) + ''')
And Same approach for @contactnumber
Please, let me know How I can optimize.
Regards
Gautam
October 16, 2010 at 4:55 am
lots of things to change there;
get rid of the old ansi join syntaxes...
get rid of the deprecated *= joins
adding the logic to split the string into a table
minor syntax errors with an extra comma
i notice you are joining to a lot of tables, but they were not referenced in the columns selected...i assume that's so you can add more columns later. otherwise some of the joins are probably not needed.
here's my version, including the split function to chop up the parameter:
declare @callerName varchar(2000)
set @callerName = 'Bob,Tom,Cathy,Karen'
select
csext_id1 as CALLERNAME,
csext_id2 as CONTACTNUMBER,
csext_id12 as POLICYNUMBER
FROM tblCases cs
INNER JOIN tblCaseCustomAttributes ca ON cs.cs_cid = ca.csext_cs_cid
LEFT OUTER JOIN tblCaseHistory cshy ON cs.cs_cid = cshy.cshy_cs_cid
INNER JOIN tblProcess pr ON cs.cs_pr_pid = pr.pr_pid
INNER JOIN dbo.tblteam tm ON pr.pr_tm_tid = tm.tm_tid
LEFT OUTER JOIN tblProcess subpr ON cs.cs_sub_pr_pid = subpr.pr_pid
WHERE csext_id1 IN (SELECT myfn.Item from dbo.DelimitedSplit8K(@callerName, ',' ) myfn )
--DelimitedSplit8K function
CREATE FUNCTION dbo.DelimitedSplit8K
(
@pString VARCHAR(MAX), --modified data type to VARCHAR(MAX)
@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
--===== "Inline" CTE Driven "Tally Table” produces values up to
-- 10,000... enough to cover VARCHAR(8000)
WITH
E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
E6(N) AS (SELECT 1 FROM E4 a, E2 b), --added to produce 1,000,000 rows
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E6)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply