SQL SERVER2000 A "WHERE" statement under condition

[duchovnick]

Hi,
In my procedure I have a query where i select the same columns dependentless of the
parameter the procedure accepts. What changes is the "where" statement.
My procedure looks something like:

create PROCEDURE dbo.myproc @myparam varchar(10), @mytable varchar(10)
AS
BEGIN TRANSACTION
DECLARE @MYdynamic VARCHAR(1000)
SET @MYdynamic = 'SELECT name
                  FROM 
		  ['+@mytable+']
		  WHERE
                    CASE '+@myparam+'
		      WHEN 1
                       'city='aaaa' AND
                        country='bbb' AND
                        lastname='ccc'
                      WHEN '2'
		        'city='bbb'
                         country='ccc'
                      END'
EXEC(@MYdynamic)

COMMIT

surely the above code is wrong and i'd be gratefull to anyone that will correct it.
Thanks

 

[r937]

SELECT name
  FROM ['+@mytable+']
 WHERE city 
       = CASE '+@myparam+'
           WHEN 1 THEN 'aaaa' 
           WHEN 2 THEN 'bbb' 
           ELSE city END
   AND country 
       = CASE '+@myparam+'
           WHEN 1 THEN 'bbb' 
           WHEN 2 THEN 'ccc' 
           ELSE country END
   AND lastname 
       = CASE '+@myparam+'
           WHEN 1 THEN 'ccc' 
           ELSE lastname END

r937.com | rudy.ca

 

[duchovnick]

hi r937
And thanks for your respond.
i copied your code as follows:

ALTER PROCEDURE dbo.Dynamicreate_superpharm2 @myparam VARCHAR(20), 
   @tab_name VARCHAR(20), @tab_name2 VARCHAR(20)
AS
BEGIN TRANSACTION

DECLARE @dynSQL VARCHAR(2000)
SET @dynSQL = 
  '
  SELECT name 		  
  FROM
    ['+@tab_name+']                       
  WHERE
    CASE '+@myparam+'
      WHEN
        '1'
          city='aaa' AND
          city<>'bbb'
      WHEN
	  '2'
          city='bbb'
      END
  '
EXEC (@dynSQL)
COMMIT

When i tried to compile it it gave the following error message

Server: Msg 170, Level 15, State 1, Procedure Dynamicreate_superpharm2, Line 15
Line 15: Incorrect syntax near '1'.

Do you have any idea what is wrong now ?
Thanks

 

[r937]

you could try taking the quotes off '1'

you didn't have them in your first posted example, i didn't have them in my suggestion, so i'm wondering where they came from...

also, you messed up the CASE construction

you cannot say CASE foo WHEN 1 x=y AND p=q

you have to say CASE WHEN foo=1 AND x=y AND p=q

why don't you try it the way i wrote it?

r937.com | rudy.ca

 

[duchovnick]

I did declare "@myparam" as varchar(20) and in the 2nd "when" i wrote "when '2''. The first
"when" was written recklessly by me...
As for the "mess" i guess i forgot the "then"..
I'll try again.
Thanks.