Solved

T-SQL creating a dynamic where clause

Posted on 2000-05-11
3
814 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 200 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Installing Sybase IQ 16.x x64 on Windows 8.1 update1 5 1,613
SyBase Query Syntax Case When 7 151
Call sybase stored procedure from php 8 94
sybase output only middle rows 4 56
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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