November 30, 2012 at 5:17 am
d
November 30, 2012 at 5:19 am
USE [Orderex]
GO
/****** Object: StoredProcedure [dbo].[GetReverseHoldingsByStockTIDMDetailed] Script Date: 11/30/2012 12:06:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[GetReverseHoldingsByStockTIDMDetailed]
(
@user-id as varchar(7),
@LoginType as varchar(5),
@AccType varchar(3),
@AccountActive varchar(1),
@SFAClientType varchar(20),
@TIDM varchar(7),
@SEDOL varchar(7),
@responsibilityCode varchar(7),
@pageNumber int,
@pageSize int,
@sortExpression varchar(32),
@sortOrder varchar(4),
@virtualCount int OUTPUT
)
WITH RECOMPILE
AS
BEGIN
CREATE TABLE #CLIENTHOLDINGSSTOCKTIDMDETAILED(
[CLIENTCODE] int NULL ,
[FullName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExecCode] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExecName][varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SFAClientType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT #CLIENTHOLDINGSSTOCKTIDMDETAILED
(CLIENTCODE,FullName,ShortName,ExecCode,ExecName,SFAClientType)
SELECT
CONVERT(INT,A.Acct_no),
A.Client_name,
A.ClientShortName,
A.Remisier_code,
B.Client_name,
CASEWHEN SFAClientType = 'E' THEN '(E) Exec'
WHEN SFAClientType = 'M' THEN '(M) Adv Mgd'
WHEN SFAClientType = 'A' THEN '(A) Adv'
WHEN SFAClientType = 'D' THEN '(D) Disc'
WHEN SFAClientType = 'B' THEN '(B) Bus'
WHEN SFAClientType = 'N' THEN '(N) Disc - N/F'
WHEN SFAClientType = 'F' THEN '(F) F.Cli.'
WHEN SFAClientType = 'X' THEN '(X) Exp'
WHEN SFAClientType = 'I' THEN '(I) Ind Cust'
WHEN SFAClientType = 'O' THEN '(O) Ord'
WHEN SFAClientType = 'S' THEN '(S) STC'
ELSE '(--) Udf' END AS SFAClientType
FROM [Orderex].[dbo].[Client_oe_info] A
INNER JOIN Awol.dbo.RR_CODES AS B ON A.Remisier_code = B.Acct_no
WHERE CONVERT(INT,A.Acct_no) NOT IN (
SELECT HoldingsInclNT.AccountNumber
FROM (
SELECT CONVERT(INT,ACC_NUMBER) AS AccountNumber, TIDM,SEDOL,CODE_ASCII FROM ALL_HOLDINGS
inner join client_oe_info coe on CONVERT(INT,coe.Acct_no) = ACC_NUMBER where coe.Remisier_code = @responsibilityCode
UNION
SELECT CONVERT(INT,ACC_NUMBER) AS AccountNumber, TIDM,SEDOL,CODE_ASCII FROM ALL_NT_HOLDINGS
) HoldingsInclNT
WHERE (TIDM = @TIDM OR SEDOL = @SEDOL)
AND code_ascii = Cast(Ascii(@AccType) as VarChar(3))
)
AND (
@LoginType = 'Staff'
AND A.Remisier_code = (CASE WHEN @responsibilityCode = 'All' THEN A.Remisier_code ELSE @responsibilityCode END)
AND A.ACCOUNTACTIVE = (CASE WHEN @AccountActive = 'A' THEN A.ACCOUNTACTIVE ELSE @AccountActive END)
)
OR
(
@LoginType = 'Assoc'
AND (@responsibilityCode = 'All'
AND (A.REMISIER_CODE IN (SELECT RR_Code FROM awol.dbo.RR_Matrix WHERE Alias_RR_Code = @user-id) OR A.REMISIER_CODE = @user-id)
OR A.REMISIER_CODE = @responsibilityCode
)
AND A.ACCOUNTACTIVE = (CASE WHEN @AccountActive = 'A' THEN A.ACCOUNTACTIVE ELSE @AccountActive END)
)
/*
Make sure that the page number is at least 1
*/
IF @pageNumber < 1
BEGIN
SET @pageNumber = 1
END
SELECT @virtualCount = COUNT(CLIENTCODE)
FROM [#CLIENTHOLDINGSSTOCKTIDMDETAILED]
WHERE SFAClientType LIKE (CASE WHEN @SFAClientType = 'ALL' THEN '%' ELSE @SFAClientType END)
DECLARE @lastKeyValue numeric(18,0)
DECLARE @lastAscendingSortValue SQL_Variant
DECLARE @lastDescendingSortValue SQL_Variant
DECLARE @numberToIgnore int
SET @numberToIgnore = (@pageNumber-1) * @pageSize
IF @numberToIgnore > 0
BEGIN
/*
Get the last available sort data and unique key
value from the last page.
*/
SET ROWCOUNT @numberToIgnore
SELECT
@lastKeyValue = [UniqueValue],
@lastAscendingSortValue = [AscendingSort],
@lastDescendingSortValue = [DescendingSort]
FROM
(
SELECT CLIENTCODE As [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CLIENTCODE'
THEN
CONVERT(SQL_Variant, CLIENTCODE)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'FullName'
THEN
CONVERT(SQL_Variant, FullName)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ShortName'
THEN
CONVERT(SQL_Variant, ShortName)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ExecCode'
THEN
CONVERT(SQL_Variant, ExecCode)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ExecName'
THEN
CONVERT(SQL_Variant, ExecName)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'SFAClientType'
THEN
CONVERT(SQL_Variant, SFAClientType)
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CLIENTCODE'
THEN
CONVERT(SQL_Variant, CLIENTCODE)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'FullName'
THEN
CONVERT(SQL_Variant, FullName)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ShortName'
THEN
CONVERT(SQL_Variant, ShortName)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ExecCode'
THEN
CONVERT(SQL_Variant, ExecCode)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ExecName'
THEN
CONVERT(SQL_Variant, ExecName)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'SFAClientType'
THEN
CONVERT(SQL_Variant, SFAClientType)
ELSE
NULL
END AS [AscendingSort]
FROM #CLIENTHOLDINGSSTOCKTIDMDETAILED
WHERE SFAClientType LIKE (CASE WHEN @SFAClientType = 'ALL' THEN '%' ELSE @SFAClientType END)
) AS Derived
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[UniqueValue] ASC
END
/*
Select the first @pageSize records that come after the last sort
data/unique value from the last page. If this is the first page,
just get the first @pageSize records.
*/
SET ROWCOUNT @pageSize
SELECT CLIENTCODE, FullName,ShortName,ExecCode,ExecName, SFAClientType
FROM
(
SELECT RIGHT('000000'+ CONVERT(VARCHAR,CLIENTCODE),7) AS CLIENTCODE,
FullName,
ShortName,
ExecCode,
ExecName,
SFAClientType,
CLIENTCODE As [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CLIENTCODE'
THEN
CONVERT(SQL_Variant, CLIENTCODE)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'FullName'
THEN
CONVERT(SQL_Variant, FullName)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ShortName'
THEN
CONVERT(SQL_Variant, ShortName)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ExecCode'
THEN
CONVERT(SQL_Variant, ExecCode)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ExecName'
THEN
CONVERT(SQL_Variant, ExecName)
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'SFAClientType'
THEN
CONVERT(SQL_Variant, SFAClientType)
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CLIENTCODE'
THEN
CONVERT(SQL_Variant, CLIENTCODE)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'FullName'
THEN
CONVERT(SQL_Variant, FullName)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ShortName'
THEN
CONVERT(SQL_Variant, ShortName)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ExecCode'
THEN
CONVERT(SQL_Variant, ExecCode)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ExecName'
THEN
CONVERT(SQL_Variant, ExecName)
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'SFAClientType'
THEN
CONVERT(SQL_Variant, SFAClientType)
ELSE
NULL
END AS [AscendingSort]
FROM #CLIENTHOLDINGSSTOCKTIDMDETAILED
WHERE SFAClientType LIKE (CASE WHEN @SFAClientType = 'ALL' THEN '%' ELSE @SFAClientType END)
) Derived
WHERE
(
@NumberToIgnore = 0
)
OR
(
@sortOrder='ASC'
AND
(
(
@lastAscendingSortValue IS NULL
AND
(
([UniqueValue] > @lastKeyValue AND [AscendingSort] IS NULL)
OR
[AscendingSort] IS NOT NULL
)
)
OR
(
@lastAscendingSortValue IS NOT NULL
AND
(
[AscendingSort] > @lastAscendingSortValue
OR
([AscendingSort] = @lastAscendingSortValue AND [UniqueValue] > @lastKeyValue)
)
)
)
)
OR
(
@sortOrder='DESC'
AND
(
(
@lastDescendingSortValue IS NOT NULL
AND (
([DescendingSort] IS NOT NULL AND
([DescendingSort] = @lastDescendingSortValue AND [UniqueValue] > @lastKeyValue)
OR
([DescendingSort] < @lastDescendingSortValue)
)
OR
([DescendingSort] IS NULL)
)
)
OR
(@lastDescendingSortValue IS NULL AND [DescendingSort] IS NULL AND [UniqueValue] > @lastKeyValue)
)
)
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[UniqueValue] ASC
SET ROWCOUNT 0
END
GO
November 30, 2012 at 5:23 am
freecoder (11/30/2012)
ok I tried your query and the result has not change at all. I change the responsibilitycode parameter to a specific one instead of 'ALL' the query works fine but it gives me a problem when I choose certain account types.I am including the execution plan for the query where it takes 1 min.
I have indexed on the tables as well.
You probably didn't understand me correctly.
I have not asked you to change your parameter, but remove it completely!
Try a query, which doesn't check input parameters at all. Code it as it would be for the most "relaxed" combination of input parameters. You may find that 1 min is mostly required to fetch a large volume of data in case of parameters set to 'ALL'.
November 30, 2012 at 5:29 am
a
November 30, 2012 at 5:31 am
I have also noticed that your code is changes from post to post 😉
Now I can see that you also trying to page it.
Please note:
SET ROWCOUNT is not the best way to do paging in SQL, consider changing it to use TOP and ROW_NUMBER().
Actually, I think you might benefit a lot if you rewrite your sp to use dynamic sql...
Unfortunately, I cannot download attached files at work, so cannot see your query plan...
November 30, 2012 at 5:37 am
d
November 30, 2012 at 5:50 am
How many records does query return without parameters?
Looking the latest version of your sp, I can tell that it has quite few things...
1. Brackets in WHERE clause of your "temp table" populating query doesn't look right.
You need to have additional set of brackets around two parts of OR
2. Is your database a case sensitive? I doubt it as your checks for:
AND UPPER(@sortExpression) = 'ExecCode'
would never work.
If not, then it's no point to UPPER your strings for comparison... It just adds more noise to the code.
Actually because of wrong brackets, the query you run without parameters is not really equal to the original one with parameters set to "ALL", it may return much less records due to the OR condition
November 30, 2012 at 5:52 am
freecoder (11/30/2012)
can u please help me! I have never written dynamic sql. And I dont have time to learn :(.
Can you give it to someone else to do? Surely if your company uses SQL Server it should employ someone with skills...
November 30, 2012 at 6:01 am
its cool mate I found an article
http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply