Solved

T-SQL creating a dynamic where clause

Posted on 2000-05-11
3
809 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
3 Comments
 
LVL 1

Expert Comment

by:albertsp
Comment Utility
What Sybase version are you using??

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

Expert Comment

by:jkotek
Comment Utility
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 200 total points
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now