September 10, 2012 at 8:09 am
hi
i have a proc which i need to include/exclude specific the account codes based on the being free of charge accounts
so....
if @FOCYN = 1
set @foc1 = '40015'
set @foc2 = '40017'
set @foc3 = '99%'
if @FOCYN = 2
set @foc1 = 'xxx'
set @foc2 = 'xxx'
set @foc3 = 'xxx'
...i also get some other variables from the user then try this which doesn't work, free of charge accounts still appear in my report....
SELECT Year, StkCode, sum(InvQty) InvQty, sum(InvVal) InvVal, sum(InvCost) InvCost, sum(ProfMargin) ProfMargin
FROM MyTable
WHERE InvYear = @YR AND InvMthNo <= @MTH
AND HomeExport IN (@DIV)
AND (Company like(@CMP) or Company is null)
AND (Area like(@AREA) or Area is null)
AND AccNo <> @FOC1
AND AccNo <> @FOC2
AND AccNo not like @FOC3
...but this works fine if i specify the accounts in the where clause...
SELECT Year, StkCode, sum(InvQty) InvQty, sum(InvVal) InvVal, sum(InvCost) InvCost, sum(ProfMargin) ProfMargin
FROM MyTable
WHERE InvYear = @YR AND InvMthNo <= @MTH
AND HomeExport IN (@DIV)
AND (Company like(@CMP) or Company is null)
AND (Area like(@AREA) or Area is null)
AND AccNo <> ('40017')
AND AccNo <> ('40015')
AND AccNo not like '99%'
..am i doing something wrong??
Thanks
September 10, 2012 at 8:24 am
spin (9/10/2012)
hii have a proc which i need to include/exclude specific the account codes based on the being free of charge accounts
so....
if @FOCYN = 1
set @foc1 = '40015'
set @foc2 = '40017'
set @foc3 = '99%'
if @FOCYN = 2
set @foc1 = 'xxx'
set @foc2 = 'xxx'
set @foc3 = 'xxx'...i also get some other variables from the user then try this which doesn't work, free of charge accounts still appear in my report....
SELECT Year, StkCode, sum(InvQty) InvQty, sum(InvVal) InvVal, sum(InvCost) InvCost, sum(ProfMargin) ProfMargin
FROM MyTable
WHERE InvYear = @YR AND InvMthNo <= @MTH
AND HomeExport IN (@DIV)
AND (Company like(@CMP) or Company is null)
AND (Area like(@AREA) or Area is null)
AND AccNo <> @FOC1
AND AccNo <> @FOC2
AND AccNo not like @FOC3...but this works fine if i specify the accounts in the where clause...
SELECT Year, StkCode, sum(InvQty) InvQty, sum(InvVal) InvVal, sum(InvCost) InvCost, sum(ProfMargin) ProfMargin
FROM MyTable
WHERE InvYear = @YR AND InvMthNo <= @MTH
AND HomeExport IN (@DIV)
AND (Company like(@CMP) or Company is null)
AND (Area like(@AREA) or Area is null)
AND AccNo <> ('40017')
AND AccNo <> ('40015')
AND AccNo not like '99%'..am i doing something wrong??
Thanks
Hard to say, not enough informationi and nothing to test against to try and ceate the issue you are having. If you could post the DDL for the table, some sample data for the table as a series of insert into statements, the code in the procedure along with sample input to the procedure, and the expected results based on the sample data and input, we might be able to help you.
Other than that, I can't even give you a shot in the dark.
September 10, 2012 at 8:34 am
As Lynn said we need more details to answer your specific issue. I do see some serious performance issues with your query. You have a lot of conditions that cause your query to be nonSARGable. This appears to be somewhat of a catch all query. Gail has an awesome post on how to improve the performance of this type of query in addition to some details about making sure the logic is correct. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2012 at 8:54 am
My guess is that you're not assigning the correct values to your variables. You're overriding your values every time.
Try this:
if @FOCYN = 1
BEGIN
set @foc1 = '40015'
set @foc2 = '40017'
set @foc3 = '99%'
END
if @FOCYN = 2
BEGIN
set @foc1 = 'xxx'
set @foc2 = 'xxx'
set @foc3 = 'xxx'
END
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy