different select based on case

I have an SP that accepts a single input param.
I want to run a different select based on it's value.

create procedure blahblah
@type varchar(100)
as
select case @type
when 'all' then
    select etc
when 'unassigned' then
   select etc
else
   select etc
end case

but it's riddled with errors.
Syntax error or trying the impossible?
LVL 29
QPRAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
you can't do that directly

create procedure blahblah
@type varchar(100)
AS

DECLARE @sql VARCHAR(8000)
SELECT @SQL  =
 case @type
when 'all' then
    'select etc'
when 'unassigned' then
   'select etc'
else
  'select etc'
end
EXEC @SQL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Atlanta_MikeCommented:
The best way to accomplish this is as follows:

If @type = 'ALL'
begin
SELECT * FROM TableName
end
else
if @Type = 'A'
begin
SELECT * FROM TableName WHERE FieldName = 'SomeValue'
end
else
if @type = 'B'
begin
SELECT * FROM TableName WHERE FieldName = 'AnotherValue'
end

0
QPRAuthor Commented:
EXEC (@sql)

thanks
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Atlanta_MikeCommented:
There are other possibilies based upon your data...
0
Atlanta_MikeCommented:
Actually... try to stay away from dynamic SQL if at all possible! You're killing performance. (either syntax :-) )
0
QPRAuthor Commented:
Actually I accepted the first answer as it worked and I half guessed it was the solution.
That said after then reading yours and weighing them both up I went with your solution... but don't tell anyone :)
0
Atlanta_MikeCommented:
Should be so quick to accept a solution until you have several options to evaluate.
0
QPRAuthor Commented:
should or shouldn't?
As I said I had a feeling that the answer would be dynamic sql.
Saw the post, tried it and it worked.
0
Atlanta_MikeCommented:
Shouldn't :-)  ... and there is almost always a better solution than using dynamic SQL.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.