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?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database 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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.