July 18, 2008 at 5:38 am
Have a look at this query:
SELECT SA.Store_code AS Store_Code,
SA.[Name] AS Store_Name ,
DTP.TRANSTYPE AS Transtype,
TT.description AS Trans_Description,
DTP.CAIS AS CAIS,
DTP.TRANSNUM AS TRANSNUM ,
DTP.LINE_ID As LINE_ID,
DTP.PRODUCT_ID AS PRODUCT_ID,
P.Product_Code AS Product_Code,
DTP.PRODUCT_SCANNED AS PRODUCT_SCANNED,
C.description AS Color_Description,
SZ.description AS [Size],
DTP.QTY AS QTY,
DTP.PRICE_ORIGINAL AS PRICE_ORIGINAL,
DTP.Price_Permanent AS Price_Permanent,
DTP.Price_Promotional AS Price_Promotional,
DTP.Price_Sold AS Price_Sold,
DTP.TAX_CATEGORY_ID AS TAX_CATEGORY_ID,
dbo.PLUS_F_FormatDate(DTP.PROD_PICK_DATE) AS PROD_PICK_DATE,
DTP.RETURN_REASON_ID AS RETURN_REASON_ID,
R.Reason_Code AS Reason_Code,
R.Description AS Reason_Description,
SA.Store_code AS Bin_Store_Code,
DTP.RETURN_SALESCODE_ID AS RETURN_SALESCODE_ID,
DTP.SERIAL_ID AS SERIAL_ID,
DTP.SPECIAL AS SPECIAL,
DTP.UPDTOTLS AS UPDTOTLS,
DTP.LEVEL1_ID AS LEVEL1_ID,
DTP.LEVEL3_ID AS LEVEL3_ID,
DTP.LEVEL4_ID AS LEVEL4_ID,
DTP.LEVEL2_ID AS LEVEL2_ID,
DTP.PACK_ID AS PACK_ID,
DTP.PACK_SEQUENCE AS PACK_SEQUENCE,
DTP.BIN_ID AS BIN_ID,
BH.Description AS Bin_Description,
DTP.PICK_LATER AS PICK_LATER,
ST.Store_code AS Return_Store_Id,
DTP.Number_of_point AS Number_of_point,
DTP.TRANSMIT AS Transmit,
DTP.TRANSMIT_SA AS TRANSMIT_SA,
dbo.PLUS_F_FormatDate(DTP.TS_ID) AS TS_ID,
DTP.Qty_Related AS Qty_Related,
DTP.Qty_Related2 AS Qty_Related2,
DTP.Return_Transnum AS Return_Transnum,
DTP.Return_Cais AS Return_Cais,
DTP.Avg_Cost_Unit_Sold AS Avg_Cost_Unit_Sold,
DTP.PRCH_SessionNumber AS PRCH_SessionNumber,
DTP.Tax_Amount1 AS Tax_Amount1,
DTP.Tax_Amount2 AS Tax_Amount2,
DTP.Tax_Amount3 AS Tax_Amount3,
DTP.Stock_Updated AS Stock_Updated,
DTP.Global_Tax_Code AS Global_Tax_Code,
T1.description AS Global_Tax_Description,
T1.Tax_Rate AS Tax_Rate,
DTP.Region_Tax_Code AS Region_Tax_Code,
T2.description AS Region_Tax_Description,
T2.Tax_Rate AS Region_Tax_Rate,
DTP.TaxCumulated AS TaxCumulated,
DTP.PackQty AS PackQty,
dbo.PLUS_F_FormatDate(DTP.PostingDate) AS PostingDate,
DTP.Return_Line_Id AS Return_Line_Id,
RL.LINE_ID AS Retali_LIne_Id,
RL.STAFF_ID AS STAFF_ID,
RL.LINE_USERVIEW AS LINE_USERVIEW,
RL.LINETYPE AS LINETYPE,
RL.IS_VISIBLE AS IS_VISIBLE,
RL.VOID_FLAG AS VOID_FLAG,
RL.VOID_REF_LINE_ID AS VOID_REF_LINE_ID,
RL.Pack_ID AS Retail_Pack_Id,
RL.Pack_Sequence AS Retail_Pack_Sequence,
RL.Gift_Sold_Id AS Gift_Sold_Id,
DTG.Store_Code_Id AS Gift_Code_Id,
DTG.Gift_Sequence AS Gift_Sequence,
DTG.Amount_Sold AS Gift_Amount_Sold,
DTG.TRansdate AS Gift_Transdate,
DTG.Transtime AS Gift_Transtime,
RL.External_LineNum AS External_LineNum,
RL.Ref_LineID AS Ref_LineID,
RL.context_reason_id AS context_reason_id,
DTPH.DT_PromotionHeaderID AS DT_PromotionHeaderID,
DTPH.TotalAdvantageValue AS TotalAdvantageValue,
DTS.DISCOUNT_ID AS DISCOUNT_ID ,
DTS.AMOUNT_DISCOUNT AS AMOUNT_DISCOUNT,
DTS.PERCENT_DISCOUNT AS PERCENT_DISCOUNT,
DTS.NEWPRICE_DISCOUNT AS NEWPRICE_DISCOUNT,
DTS.DESCRIPTION AS DESCRIPTION,
CASE
WHEN (DTP.Price_Permanent- ROUND(DTP.Price_Permanent,0,1))=.00 THEN
CASE
WHEN SUBSTRING(SA.STORE_CODE,1,1)=1 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=6 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=3 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=5 THEN 'N' END
WHEN (DTP.Price_Permanent- ROUND(DTP.Price_Permanent,0,1))=.50 THEN
CASE
WHEN SUBSTRING(SA.STORE_CODE,1,1)=1 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=6 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=3 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=5 THEN 'N' END
WHEN (DTP.Price_Permanent- ROUND(DTP.Price_Permanent,0,1))=.99 THEN
CASE
WHEN SUBSTRING(SA.STORE_CODE,1,1)=1 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=6 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=3 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=5 THEN 'N' END
ELSE 'Y'
END AS MARKDOWN
FROM dbo.Dt_product DTP
INNER JOIN dbo.Taxes T1 ON DTP.Global_Tax_Code = T1.Tax_Region_Id
INNER JOIN dbo.Taxes T2 ON DTP.Region_Tax_Code = T2.Tax_Region_Id
INNER JOIN dbo.Store SA ON SA.store_code_id=DTP.store_code_id
INNER JOIN dbo.Store ST ON DTP.Return_Store_id= ST.Store_code_ID
INNER JOIN dbo.Color C ON DTP.Color_ID = C.Color_ID
INNER JOIN dbo.Product P ON DTP.product_id=P. product_id
INNER JOIN dbo.[Size] SZ ON DTP.SIZE_ID=SZ.Size_ID
INNER JOIN dbo.Reasons R ON R.Reason_Id = DTP.Return_Reason_Id
INNER JOIN dbo.Bin_Header BH ON BH.Bin_ID = DTP.Bin_Id
INNER JOIN dbo.Retail_Line RL ON RL.store_code_id = DTP.Store_code_id
AND RL.TRANSTYPE = DTP.TRANSTYPE
AND RL.CAIS = DTP.CAIS
AND RL.TRANSNUM = DTP.TRANSNUM
AND RL.LINE_ID = DTP.LINE_ID
INNER JOIN dbo.Transaction_Type TT ON RL.TRANSTYPE = TT.TRANSTYPE
LEFT OUTER JOIN dbo.DT_Gift DTG ON RL.Gift_Sold_ID =DTG.Gift_Sold_ID
LEFT OUTER JOIN dbo.DT_PromotionHeader DTPH ON RL.STORE_CODE_ID = DTPH.STORE_CODE_ID
AND RL.TRANSTYPE = DTPH.TRANSTYPE
AND RL.CAIS = DTPH.CAIS
AND RL.TRANSNUM = DTPH.TRANSNUM
AND RL.LINE_ID = DTPH.LINE_ID
LEFT OUTER JOIN dbo.dt_Disc DTS ON RL.STORE_CODE_ID = DTS.STORE_CODE_ID
AND RL.TRANSTYPE = DTS.TRANSTYPE
AND RL.CAIS = DTS.CAIS
AND RL.TRANSNUM = DTS.TRANSNUM
AND RL.LINE_ID = DTS.LINE_ID
WHERE DATEDIFF(d,CONVERT (VARCHAR(10) ,RL.TS_ID,101), (SELECT CONVERT(VARCHAR(10),Last_Successful_Run_Date,101) FROM dbo.tbl_Load_Audit_History WHERE [Object_Name]='PLUS_IDO_SALES_DETAILS' AND System_Name='PivotLink' AND IsActive=1)) <0
AND DATEDIFF(d,CONVERT (VARCHAR(10) ,RL.TS_ID,101), CONVERT(VARCHAR(10),'2008-06-18',101))>= 0
i have given the attachement which consists of the query statistics with showplan_all. Please suggest me the course of action.
July 18, 2008 at 5:41 am
Please post the table and index definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 5:45 am
this select query uses many tables , do you want all the table and index definitions...cant you see the exec plan and be particular as to which particular table/index is using most of the query cost..
July 18, 2008 at 5:47 am
All of them please. Zipped and attached it they're that big.
I'm less interested in the indexes it did use than the index it might not be using that could be more effective.
Debugging perf problems without the table defs and indexes is difficult.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 6:15 am
any you u advice me, how do i look, whether the correct index has been used rather..?:)
July 18, 2008 at 6:35 am
Can you post the table structures creation script (please include the current indexes creation script also)
As i've observed there are too many joins
I would suggest why not try breaking up the query into two parts or three parts
Sometimes it would help when we break it into two queries
It would be of gr8 help if you can post the table creation scripts
so that we can check the execution plans ourselves
July 18, 2008 at 6:39 am
As a first pass, that the query is using index seeks, not scans and there are no lookups.
I notice that all but one of the indx operations are full scans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 8:22 am
Looks pretty heavy work for SQL Server optimizer.
Do the following Steps
1 - Check all the join conditions. just shuffle all the joining area
where it fetches less data
Ex if u have 12 join conditions in the query.See to it that which join condition fetches the less data and arange as accordingly
2 And most important things. Lets suppose u have indexed all the tables
A -- see that the joining columns has index or not,if not put the
non clustered index on it
B-- See that the arrangements of joining columns should have the same arrangement of indexes created in the underlying tables
Ex Select a.col1,a.col2,b.col1,b.col2 from a
join b
on col1 = col1
and col2 = col2
And the indexes for this columns in the tables should be
Like this
create index on a (col1,col2)
create index on b (Col1,col2)
Make sure about the order of columns indexed in the table and order of columns in query
I hope this may help u .
Do let me know. after checking it
Altaf
July 18, 2008 at 1:44 pm
Probably the main reason it's running so slowly is this:
DATEDIFF(d,CONVERT (VARCHAR(10) ,RL.TS_ID,101),
(SELECT CONVERT(VARCHAR(10),Last_Successful_Run_Date,101)
FROM dbo.tbl_Load_Audit_History WHERE [Object_Name]='PLUS_IDO_SALES_DETAILS' AND System_Name='PivotLink' AND IsActive=1)) <0
(From the Where clause.)
First, it's a correlated sub-query in the Where clause. That's generally a bad idea. Second, you're doing datediff(day), but first you convert the dates into varchar and then force an implicit conversion back to datetime.
The conversion to varchar is done to eliminate the time on the dates, which doesn't need to be done for datediff(day).
select datediff(day, '1/1/2000', '1/2/2000'),
datediff(day, '1/1/2000 12:37 pm', '1/2/2000 8 am')
Both give 1. Make the times in the second datediff any values you want (so long as they are legal for datetime), but if you leave the date part of it alone, it will continue to give you 1 every time.
It looks like what this part of the query is supposed to do is find the last run date for something or other, and make sure that all the data being pulled is later than that. You might be better off doing that as a simple join.
There may be more wrong with the query, but that part leapt out at me as a probable culprit.
Is Retail_Line.TS_ID a datetime column?
- 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 18, 2008 at 1:50 pm
The nested Case statements might also be a minor problem. Look at changing to a complex Case with multiple conditions instead of multiple nested Cases.
Try this, see if it gets the results you need:
[/code]
CASE
WHEN (DTP.Price_Permanent - ROUND(DTP.Price_Permanent,0,1)) in (0,.5,.99)
AND LEFT(SA.STORE_CODE,1) in ('1', '6', '3', '5')
THEN 'N'
ELSE 'Y'
END AS MARKDOWN
[/code]
- 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 22, 2008 at 3:16 am
hi everybody, with some minor changes as suggested by you ppl, the query executed in 4 min :D, here is the updated query, the problem is that we would be giving the below select query in a view, so i guess , we cannot use variables in a view right..? need some ideas ...
declare @@dt1 datetime
set @@dt1 =
( SELECT Last_Successful_Run_Date FROM dbo.tbl_Load_Audit_History WHERE [Object_Name]='PLUS_IDO_SALES_DETAILS' AND System_Name='PivotLink' AND IsActive=1 )
--Select @@dt1
SELECT SA.Store_code AS Store_Code,
SA.[Name] AS Store_Name ,
DTP.TRANSTYPE AS Transtype,
TT.description AS Trans_Description,
DTP.CAIS AS CAIS,
DTP.TRANSNUM AS TRANSNUM ,
DTP.LINE_ID As LINE_ID,
DTP.PRODUCT_ID AS PRODUCT_ID,
P.Product_Code AS Product_Code,
DTP.PRODUCT_SCANNED AS PRODUCT_SCANNED,
C.description AS Color_Description,
SZ.description AS [Size],
DTP.QTY AS QTY,
DTP.PRICE_ORIGINAL AS PRICE_ORIGINAL,
DTP.Price_Permanent AS Price_Permanent,
DTP.Price_Promotional AS Price_Promotional,
DTP.Price_Sold AS Price_Sold,
DTP.TAX_CATEGORY_ID AS TAX_CATEGORY_ID,
dbo.PLUS_F_FormatDate(DTP.PROD_PICK_DATE) AS PROD_PICK_DATE,
DTP.RETURN_REASON_ID AS RETURN_REASON_ID,
R.Reason_Code AS Reason_Code,
R.Description AS Reason_Description,
SA.Store_code AS Bin_Store_Code,
DTP.RETURN_SALESCODE_ID AS RETURN_SALESCODE_ID,
DTP.SERIAL_ID AS SERIAL_ID,
DTP.SPECIAL AS SPECIAL,
DTP.UPDTOTLS AS UPDTOTLS,
DTP.LEVEL1_ID AS LEVEL1_ID,
DTP.LEVEL3_ID AS LEVEL3_ID,
DTP.LEVEL4_ID AS LEVEL4_ID,
DTP.LEVEL2_ID AS LEVEL2_ID,
DTP.PACK_ID AS PACK_ID,
DTP.PACK_SEQUENCE AS PACK_SEQUENCE,
DTP.BIN_ID AS BIN_ID,
BH.Description AS Bin_Description,
DTP.PICK_LATER AS PICK_LATER,
ST.Store_code AS Return_Store_Id,
DTP.Number_of_point AS Number_of_point,
DTP.TRANSMIT AS Transmit,
DTP.TRANSMIT_SA AS TRANSMIT_SA,
dbo.PLUS_F_FormatDate(DTP.TS_ID) AS TS_ID,
DTP.Qty_Related AS Qty_Related,
DTP.Qty_Related2 AS Qty_Related2,
DTP.Return_Transnum AS Return_Transnum,
DTP.Return_Cais AS Return_Cais,
DTP.Avg_Cost_Unit_Sold AS Avg_Cost_Unit_Sold,
DTP.PRCH_SessionNumber AS PRCH_SessionNumber,
DTP.Tax_Amount1 AS Tax_Amount1,
DTP.Tax_Amount2 AS Tax_Amount2,
DTP.Tax_Amount3 AS Tax_Amount3,
DTP.Stock_Updated AS Stock_Updated,
DTP.Global_Tax_Code AS Global_Tax_Code,
T1.description AS Global_Tax_Description,
T1.Tax_Rate AS Tax_Rate,
DTP.Region_Tax_Code AS Region_Tax_Code,
T2.description AS Region_Tax_Description,
T2.Tax_Rate AS Region_Tax_Rate,
DTP.TaxCumulated AS TaxCumulated,
DTP.PackQty AS PackQty,
dbo.PLUS_F_FormatDate(DTP.PostingDate) AS PostingDate,
DTP.Return_Line_Id AS Return_Line_Id,
RL.LINE_ID AS Retali_LIne_Id,
RL.STAFF_ID AS STAFF_ID,
RL.LINE_USERVIEW AS LINE_USERVIEW,
RL.LINETYPE AS LINETYPE,
RL.IS_VISIBLE AS IS_VISIBLE,
RL.VOID_FLAG AS VOID_FLAG,
RL.VOID_REF_LINE_ID AS VOID_REF_LINE_ID,
RL.Pack_ID AS Retail_Pack_Id,
RL.Pack_Sequence AS Retail_Pack_Sequence,
RL.Gift_Sold_Id AS Gift_Sold_Id,
DTG.Store_Code_Id AS Gift_Code_Id,
DTG.Gift_Sequence AS Gift_Sequence,
DTG.Amount_Sold AS Gift_Amount_Sold,
DTG.TRansdate AS Gift_Transdate,
DTG.Transtime AS Gift_Transtime,
RL.External_LineNum AS External_LineNum,
RL.Ref_LineID AS Ref_LineID,
RL.context_reason_id AS context_reason_id,
DTPH.DT_PromotionHeaderID AS DT_PromotionHeaderID,
DTPH.TotalAdvantageValue AS TotalAdvantageValue,
DTS.DISCOUNT_ID AS DISCOUNT_ID ,
DTS.AMOUNT_DISCOUNT AS AMOUNT_DISCOUNT,
DTS.PERCENT_DISCOUNT AS PERCENT_DISCOUNT,
DTS.NEWPRICE_DISCOUNT AS NEWPRICE_DISCOUNT,
DTS.DESCRIPTION AS DESCRIPTION,
CASE
WHEN (DTP.Price_Permanent- ROUND(DTP.Price_Permanent,0,1))=.00 THEN
CASE
WHEN SUBSTRING(SA.STORE_CODE,1,1)=1 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=6 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=3 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=5 THEN 'N' END
WHEN (DTP.Price_Permanent- ROUND(DTP.Price_Permanent,0,1))=.50 THEN
CASE
WHEN SUBSTRING(SA.STORE_CODE,1,1)=1 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=6 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=3 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=5 THEN 'N' END
WHEN (DTP.Price_Permanent- ROUND(DTP.Price_Permanent,0,1))=.99 THEN
CASE
WHEN SUBSTRING(SA.STORE_CODE,1,1)=1 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=6 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=3 THEN 'N'
WHEN SUBSTRING(SA.STORE_CODE,1,1)=5 THEN 'N' END
ELSE 'Y'
END AS MARKDOWN
FROM dbo.Dt_product DTP
INNER JOIN dbo.Taxes T1 ON DTP.Global_Tax_Code = T1.Tax_Region_Id
INNER JOIN dbo.Taxes T2 ON DTP.Region_Tax_Code = T2.Tax_Region_Id
INNER JOIN dbo.Store SA ON SA.store_code_id=DTP.store_code_id
INNER JOIN dbo.Store ST ON DTP.Return_Store_id= ST.Store_code_ID
INNER JOIN dbo.Color C ON DTP.Color_ID = C.Color_ID
INNER JOIN dbo.Product P ON DTP.product_id=P. product_id
INNER JOIN dbo.[Size] SZ ON DTP.SIZE_ID=SZ.Size_ID
INNER JOIN dbo.Reasons R ON R.Reason_Id = DTP.Return_Reason_Id
INNER JOIN dbo.Bin_Header BH ON BH.Bin_ID = DTP.Bin_Id
INNER JOIN dbo.Retail_Line RL ON RL.store_code_id = DTP.Store_code_id
AND RL.TRANSTYPE = DTP.TRANSTYPE
AND RL.CAIS = DTP.CAIS
AND RL.TRANSNUM = DTP.TRANSNUM
AND RL.LINE_ID = DTP.LINE_ID
INNER JOIN dbo.Transaction_Type TT ON RL.TRANSTYPE = TT.TRANSTYPE
LEFT OUTER JOIN dbo.DT_Gift DTG ON RL.Gift_Sold_ID =DTG.Gift_Sold_ID
LEFT OUTER JOIN dbo.DT_PromotionHeader DTPH ON RL.STORE_CODE_ID = DTPH.STORE_CODE_ID
AND RL.TRANSTYPE = DTPH.TRANSTYPE
AND RL.CAIS = DTPH.CAIS
AND RL.TRANSNUM = DTPH.TRANSNUM
AND RL.LINE_ID = DTPH.LINE_ID
LEFT OUTER JOIN dbo.dt_Disc DTS ON RL.STORE_CODE_ID = DTS.STORE_CODE_ID
AND RL.TRANSTYPE = DTS.TRANSTYPE
AND RL.CAIS = DTS.CAIS
AND RL.TRANSNUM = DTS.TRANSNUM
AND RL.LINE_ID = DTS.LINE_ID
WHERE DATEDIFF(day, RL.TS_ID, @@dt1 ) <0
AND DATEDIFF(day,RL.TS_ID, '2008-06-18')>= 0
and SSCrazy you mentioned that it is better to have the lastrun part of the query as a simple join . "You might be better off doing that as a simple join."...can u explain a bit further...
July 23, 2008 at 6:50 am
1) If users don't need to join this output to other tables, then make it a stored procedure that they call.
2) WHERE DATEDIFF(day, RL.TS_ID, @@dt1 ) <0
AND DATEDIFF(day,RL.TS_ID, '2008-06-18')>= 0
You should do anything you can to avoid wrapping columns in functions, as it voids the use of index seeks. That where clause can be rewritten to do that.
3) If the input parameter (date) can vary widely (i.e. result in very few or very large numbers of output rows) I would consider switching this query to dynamic sql, which will avoid having a cached plan that is optimal for one end of the spectrum be used for a date that results in the other end of the spectrum. You can pack a lunch when you have a nested loop query plan try to hit millions of rows on multiple tables.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 23, 2008 at 1:46 pm
If you absolutely must make it a view, you can create a table to hold parameters and include a join to the parms table in your view. If multiple users will be accessing this at the same time, make the primary key to your parms table be a smallint column to hold the @@spid number, then you can do things like this:
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
insert into dbo.parms
select @@SPID,'A','B','C','123'
select * from dbo.v_table_w_parms -- this view would include a join to the parms table on pk = @@spid
-- to make them available to your view
delete dbo.spidFoo -- and remember to clean up afterwards
where spid = @@spID
good luck
__________________________________________________
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 23, 2008 at 2:24 pm
Folks... I apologize. I typed my example above off the top of my head and it is full of GLARING inconsistencies.... this is not my day to be coding, or commenting. Here is a corrected version:
CREATE TABLE [dbo].[parms](
[spid] [smallint] NOT NULL,
[parm1] [varchar](50) NULL,
[parm2] [varchar](50) NULL,
[parm3] [varchar](50) NULL,
[parm4] [varchar](50) NULL,
CONSTRAINT [PK_SpidFoo] PRIMARY KEY CLUSTERED ( [spid] ASC) etc, etc
-----------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[FooTable](
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_FooTable_ID] DEFAULT (newid()),
[animal] [varchar](10) NULL,
[fruit] [varchar](50) NULL,
[country] [varchar](50) NULL,
CONSTRAINT [PK_FooTable_1] PRIMARY KEY CLUSTERED ([ID] ASC etc, etc
-----------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[v_foo]
AS
SELECT f.ID, f.animal, f.fruit, f.country
FROM dbo.FooTable AS f WITH (nolock)
JOIN dbo.Parms AS p WITH (nolock) ON f.animal = p.parm1
or f.fruit = p.parm2
or f.country = p.parm3
WHERE (p.spid = @@SPID)
-----------------------------------------------------------------------------------------------------------------
The code below will return rows from the view where the underlying FooTable has "apple" for fruits.
insert into dbo.parms (spid,parm1)
select @@spid, 'Apple'
select * from dbo.v_foo
delete dbo.parms
where spid = @@spid
__________________________________________________
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 23, 2008 at 3:14 pm
Still looks to be rife with problems, but I will let them pass. 🙂 If this type of system has worked reliably for you in the past good for you. Not sure I would recommend such a mechanism though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply