November 23, 2004 at 6:10 pm
I'm working on a multivariable stored procedure that basically says this.
Create Procedure sp_thisprocedure
(@variable1 varchar(50), @variable2 varchar(50), @variable3 varchar(50), @ordervar varchar(100))
AS
SELECT * From A_Table
INNER JOIN B_Table on A_Table.ID = B_TableID
WHERE A_Table.ID = @variable1
If @variable2 <> ''
BEGIN
AND A_Table.ID2 = @variable2
END
IF @variable3 <> ''
BEGIN
AND B_Table.ID2 = @variable3
END
ORDER BY
CASE WHEN @ordervar ='order1' THEN A_Table.ID
WHEN @ordervar ='order2' THEN B_Table.ID
END
GO
Now when I do a syntax check on this, it always tells me there's an error at "AND". Am I doing this wrong?
November 23, 2004 at 6:46 pm
You are not allowed to use IF statements in your where clauses. I can see what you are trying to do though and maybe this will work for you:
Create Procedure sp_thisprocedure
(@variable1 varchar(50), @variable2 varchar(50), @variable3 varchar(50), @ordervar varchar(100))
AS
SELECT * From A_Table
INNER JOIN B_Table on A_Table.ID = B_TableID
WHERE A_Table.ID = @variable1
AND A_Table.ID2 = CASE WHEN @variable2 '' THEN @variable2 ELSE A_Table.ID2 END
AND B_Table.ID2 = CASE WHEN @variable3 '' THEN @variable3 ELSE B_Table.ID2 END
ORDER BY CASE WHEN @ordervar ='order1' THEN A_Table.ID
WHEN @ordervar ='order2' THEN B_Table.ID END
GO
November 23, 2004 at 7:00 pm
Thanks! That worked great.
Quick other question. Can I not use the DESC command when looking for the order? Like this?
ORDER BY CASE WHEN @ordervar ='order1' THEN A_Table.ID DESC
WHEN @ordervar ='order2' THEN B_Table.ID END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply