[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

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 !
0
cwilliams122399
Asked:
cwilliams122399
1 Solution
 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now