June 6, 2002 at 1:03 pm
Gentleman, I have a small dilemma and would interested if it can be done in sql.
Here is the deal:
1st make yourself a table
AccountID int, category1 int,category2 int.
My dilemma is this, I have written a stored procedure that utilizes a case statement to select specific data from either category1 or category2 based on my input parameters. Here is the code:
create procedure sp_store_category_s
@nAccountID int,
@nCategoryLevel int,
@nReturn int = null output
as
select distinct
case @nCategoryLevel
when 1 then category1
when 2 then category2
end as category
from
store
where
account_id = @nAccountID and category is not null
order by category
I am getting and error "invalid column name Category"
I guess this is because category is not an actual column in my table. Also if you notice in the case statement I am choosing a column to select via my @nCategoryLevel parameter.
I am guessing the SQL is getting confused in the where clause because "category" is not a real column within the table.
I know this is a bit confusing, but I really need to get this figured out. So ask question if you got em.
Thanks, Scott
June 6, 2002 at 2:56 pm
Yes it should be that it is confused by this line
account_id = @nAccountID and category is not null
Try something like
account_id = @nAccountID and
(case @nCategoryLevel
when 1 then category1
when 2 then category2
end) is not null
Also for performance reason you may be better off creating two SPs instead of one, and create a single SP like so to direct choice.
create procedure sp_store_category_s;1
@nAccountID int,
@nCategoryLevel int,
@nReturn int = null output /*did not know what you want with this.*/
as
SET NOCOUNT ON
if @nCategoryLevel = 1
BEGIN
EXEC sp_store_category_s;2
END
ELSE
BEGIN
EXEC sp_store_category_s;3
END
GO
create proc sp_store_category_s;2
@nAccountID int
AS
SET NOCOUNT ON
SELECT distinct catergory1
FROM store
WHERE
account_id = @nAccountID and category1 is not null
order by category1
GO
create proc sp_store_category_s;3
@nAccountID int
AS
SET NOCOUNT ON
SELECT distinct catergory2
FROM store
WHERE
account_id = @nAccountID and category2 is not null
order by category2
GO
The neat thing about this is that ;1,;2,;3 are technically each individual stored procedures and thus store a specific execution plan for their needs. Also when you view in EM you will only see one listed and when you open all three will be inside the edit box. This processs is called grouped procedures and I use often for logic and association. You will if you create the whole thing at one time see and error message the other tiems are not found but ;1 will still be created whioch is ok and things will still be done. This offers you a way to get a better performance out of your SP.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply