Is there any command that could do that?
thanks
[code]
CREATE PROCEDURE sa_default
@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = null
AS
if @.choix=0
begin
........
end
if @.choix=1
begin
.........
end
if @.choix=2
begin
.........
end
if @.choix=3
begin
......
end
RETURN
GO
[/code]What is it you are trying to accomplish in your condition?|||I am selection a list of project which change depending on the level of right of the user
|||You can use a CASE statement in some situations, but it seems like you want to execute code within your BEGIN... ENDs. It won't work in those cases. I don't believe there is anything like Javascript's switch you can use to execute code like that. Depending on what you're trying to do, dynamic SQL might be an answer. Really though, more information is needed on what will happen based on each IF to really give a good answer.|||A CASE statement may work when you just need to change the WHERE clause, but it looks like you change the query as well. It looks like IF times 4 is the way to go.|||I second trying the CASE, but if you can't make it work then I'd make turn those inner batches into their own procs you'll prb' get better performance cause you won't suffer from the selective query plan issues of using IFs.|||Thanks for your comments.
CREATE PROCEDURE sa_default@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = nullAS
if @.choix=0
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0 )) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tproject.locator=isnull(@.locator,locator) ORDER BY idproject
end
if @.choix=1
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and (Tuserproject.etridperson=@.etriduser or Tproject.locator=@.locator) ORDER BY idproject
end
if @.choix=2
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1,Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tuserproject.etridperson=@.etriduser ORDER BY idproject
endif @.choix=3
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1, Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) AND
(Tuserproject.etridperson IN (SELECT idperson FROM Tperson WHERE login LIKE @.login))
ORDER BY Tproject.idproject
endRETURN
GO
Can you tell me more about your idea about "turn those inner batches into their own procs "
I never hear about that, do you have any sample or link for documentation?
Thanks|||There's no great mystery to it. Problem 1: One of the publicised problems with stored procedure plans is they can get "confused" by IF statements. I believe the problem is because it originally stores only one plan that represents only one path through the code. So the other IF conditions may not be ready to run. I've not tested it this myself but sounds plausable.
Problem 2: The more complicated the plan, the more chance the query optimiser has of making the wrong choices. I've certainly seen this. The more conditions in the proc seems to increase the likelyhood of the query plan having performance issues. Not suprising really because there is a slider between compiling optimal code and compiling the code quickly. So the longer it takes to compile the code the more likely the optimiser will throw its hands in the air.
Both of these problems can be helped by converting some of the query into other stored procedures. These have their own plans so the optimiser can, typically, do a better job.|||I agree with that post 100% and it's true that 4 significantly different queries will have a very good chance of not being run optimally. Create 4 procs and within your application code, decide which one to call based on what that parameter is at run time. The 4 procs will run more efficiently.
