Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL creating a dynamic where clause

Posted on 2000-05-11
3
Medium Priority
?
819 Views
Last Modified: 2011-09-20
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
Comment
Question by:cwilliams122399
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Expert Comment

by:albertsp
ID: 2803911
What Sybase version are you using??

Dynamic SQL is not supported in the Sybase 11 versions.
0
 
LVL 2

Expert Comment

by:jkotek
ID: 2804661
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
 

Accepted Solution

by:
sbigelow earned 800 total points
ID: 2814993
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Files go missing when using DFS (Distributed File System) Replication and how to recover them and fix it.
In this article, I’ll show how research, determination, and use of modern technology helped me solve a DNA mystery.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question