T-SQL creating a dynamic where clause

I am creating a stored procedure in which I need to query a table that can be identified by taking a root table name and concatenating it with a parameter as a suffix.

Does anyone know how to construct a where clause dynamically in such a manner?
Thank you !
cwilliams122399Asked:
Who is Participating?
 
sbigelowCommented:
Here's an approach that may work:

Assuming @suffix is the parameter passed into your stored procedure:

DECLARE @SQL_SELECT

SET @SQL_SELECT = 'SELECT * FROM Table_Name_Root_' + @suffix + ' WHERE the-rest-of-your-SELECT-statement'

EXEC (@SQL_SELECT)

Two notes:
-- There is no line break when setting the value for @SQL_SELECT -- any line break inmy example above is due to word wrapping in the HTML form, rather than intentional in the assignment statement.

-- In the assignment statement, there is no space between the table name root and your passed parameter (so the end value is something like 'Table_Name_Root_ParameterValue'). There is, however, a space between the passed parameter and the beginning of the WHERE clause...

Hope this helps.
0
 
albertspCommented:
What Sybase version are you using??

Dynamic SQL is not supported in the Sybase 11 versions.
0
 
jkotekCommented:
Albertsp is refereing to the Sybase AS Enterprise. It supports this functionality only in version 12.0 (latest).
The other two db servers - AS Anywhere and AS IQ supports this (Anywhere from ver. 5.5, IQ from 12.0)

One way you can do this in ASE 11 is to use some if-then in stored procedure listing all of the possible tables.
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.