sql server 2000, dynamic sql, error message 1

[duchovnick]

hi,
i send 2 parameters to my procedure: one for identification (flag), the other is a file's name.
I want my procedure to operate a "select" querry including a "where" statement based on those parameters.
my code is:

/*exec dbo.Dynamic '1','march_3_07'*/ 
alter PROCEDURE dbo.Dynamic @myparam VARCHAR(20), 
   @tab_name VARCHAR(20)
AS
BEGIN TRANSACTION

DECLARE @dynSQL VARCHAR(2000)
SET @dynSQL = 
  '
  SELECT city           
  FROM
    ['+@tab_name+'] 
    WHERE
    CASE '+@myparam+'
      
          city=''aaa'' AND
          city<>''bbb''                      
  end
  '
exec (@dynSQL)
COMMIT

and the error message says:

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'city'.

can anyone help me to correct the "where" statement ?
Thanks.

 

[SQLDenis]

change exec to print and post the result here
why do you have a transaction on a select statement?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[duchovnick]

Hi SQLDenis
this is the print

SELECT city           
  FROM
    [march_3_07] 
    WHERE
    CASE 1
      
          city='aaa' AND
          city<>'bbb'                      
  end

i need to run this "select" query on various dates and kind of people and i thought it would go faster sending parameters to the procedure rather then change its code.
Hopefully the attached "print" will help you to save me.
And thanks!

 

[SQLDenis]

why do you have CASE 1 there?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

Your WHERE clause is incorrect, WHERE CASE 1????? is not the where clause SQL Server expect. Check the Procedure to be:

/*exec dbo.Dynamic '1','march_3_07'*/
ALTER PROCEDURE dbo.Dynamic
   @myparam VARCHAR(20),
   @tab_name VARCHAR(20)
AS
BEGIN TRANSACTION

DECLARE @dynSQL VARCHAR(2000)
SELECT @dynSQL =  'SELECT city
                          FROM ['+@tab_name+']
                          WHERE '+
                  CASE WHEN @myparam = 1 THEN
                       ' city=''aaa'' AND
                         city<>''bbb'''
                       ELSE '1=1' END                      
print @dynSQL
exec (@dynSQL)
COMMIT

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[duchovnick]

The second parameter: "@myparam" recieved that value.
I ran the procedure writing:
"exec dbo.Dynamic '1','march_3_07'". '1' is for an example. it could by 'vip' or "employees" or :"managers". It is a cretiria for an employee. Lets say i want to show all managers that live in "ny" so i send "ny" as a parmeter.

 

[duchovnick]

Thank you so much Borislav for another one of your magic codes that always
W O R K !!
I wish half of mine would...