Dynamic SQL generation in T-SQL 
Author Message
 Dynamic SQL generation in T-SQL

Hello!

I have a input variable in my Stored Procedure which stores a list of words
that are delimitted by a comma ",".


I am creating a dynamic sql statament and each of these words are a table
name in my database.

So if I had 4 words my sql statement would read

"select * from tab1 where .....
UNION
select * from tab2 were....
UNION
select * from tab3 were....
UNION
select * from tab4 were...."

The sql condition is always the same. I.e whatever is after the "where"
clause is always the same.
Anyway to split the words and store it in an array and then have a while
condition loop thru the words so I can build my sql statement? All this
should be done in T-SQL....

Thanks,
Girish



Tue, 10 Dec 2002 03:00:00 GMT
 Dynamic SQL generation in T-SQL

Girish,












---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.

Quote:

> Hello!

> I have a input variable in my Stored Procedure which stores a list of words
> that are delimitted by a comma ",".


> I am creating a dynamic sql statament and each of these words are a table
> name in my database.

> So if I had 4 words my sql statement would read

> "select * from tab1 where .....
> UNION
> select * from tab2 were....
> UNION
> select * from tab3 were....
> UNION
> select * from tab4 were...."

> The sql condition is always the same. I.e whatever is after the "where"
> clause is always the same.
> Anyway to split the words and store it in an array and then have a while
> condition loop thru the words so I can build my sql statement? All this
> should be done in T-SQL....

> Thanks,
> Girish



Tue, 10 Dec 2002 03:00:00 GMT
 Dynamic SQL generation in T-SQL
If you don't have a working solution yet, try this one.  It could be made
into a stored procedure very easily.  I won't explain much about it unless
you need me to, because it is commented inside the code.

-- CODE BEGIN --





each table be seperated by exactly 1 comma and no spaces, it could be
modified to suit other needs though

required otherwise it will run together with the table name

to change this to just: " UNION "

anything is still null






 -- the list contains at least one delimiter --> at least 2 tables are
supplied

 BEGIN
  -- if this is not the first table

   -- add the UNION statement

  -- parse next table from the list and add it to the select statement


  -- find the position of the next delimiter, and save the position of the
last one


  -- if no more delimiters found, then

  BEGIN
   -- add the UNION statement

   -- parse the last table from the list and add it to the select statement


  END
 END
ELSE
 -- the list contains at no delimiters --> only 1 table is supplied

-- for debugging only; uncomment to view the sql statement

-- execute the dynamic statement

-- CODE END --



Tue, 10 Dec 2002 03:00:00 GMT
 Dynamic SQL generation in T-SQL
Thank you, thank you and THANK you!!

:-)

Boy, why didnt I look at the charindex function beats me!

Thanks again,
Girish


Quote:
> If you don't have a working solution yet, try this one.  It could be made
> into a stored procedure very easily.  I won't explain much about it unless
> you need me to, because it is commented inside the code.

> -- CODE BEGIN --





that
> each table be seperated by exactly 1 comma and no spaces, it could be
> modified to suit other needs though

is
> required otherwise it will run together with the table name

> to change this to just: " UNION "

> anything is still null






>  -- the list contains at least one delimiter --> at least 2 tables are
> supplied

>  BEGIN
>   -- if this is not the first table

>    -- add the UNION statement

>   -- parse next table from the list and add it to the select statement


>   -- find the position of the next delimiter, and save the position of the
> last one


>   -- if no more delimiters found, then

>   BEGIN
>    -- add the UNION statement

>    -- parse the last table from the list and add it to the select
statement



>   END
>  END
> ELSE
>  -- the list contains at no delimiters --> only 1 table is supplied


> -- for debugging only; uncomment to view the sql statement

> -- execute the dynamic statement

> -- CODE END --



Wed, 11 Dec 2002 03:00:00 GMT
 Dynamic SQL generation in T-SQL
sorry for not explaining my question properly..
I was looking for a soln more like Tom Jones has posted.

Thanks very much though,
:-)
Girish


Quote:
> Girish,












> ---------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which
can be
> cut and pasted into Query Analyzer is appreciated.




- Show quoted text -

Quote:
> > Hello!

> > I have a input variable in my Stored Procedure which stores a list of
words
> > that are delimitted by a comma ",".


> > I am creating a dynamic sql statament and each of these words are a
table
> > name in my database.

> > So if I had 4 words my sql statement would read

> > "select * from tab1 where .....
> > UNION
> > select * from tab2 were....
> > UNION
> > select * from tab3 were....
> > UNION
> > select * from tab4 were...."

> > The sql condition is always the same. I.e whatever is after the "where"
> > clause is always the same.
> > Anyway to split the words and store it in an array and then have a while
> > condition loop thru the words so I can build my sql statement? All this
> > should be done in T-SQL....

> > Thanks,
> > Girish



Wed, 11 Dec 2002 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Dynamic SQL generation for Informix

2. Dynamic SQL generation is not supported for ORACLE in DOTNET

3. TSQL, dynamic SQL and cursors?

4. Differences between SyBase TSQL, and SQL Server 2000 TSQL

5. SQL Server 7.0, PB with SQL script generation

6. Automatic Generation of PL/SQL, SQL

7. PL/SQL:Efficiency of inline SQL vs use of Dynamic SQL Package

8. Ideas!!!: DBMS_SQL and dynamic snapshot generation

9. Dynamic Query Generation in Stored Procedures / Database Switching

10. Dynamic File Generation for a DTS Load

11. dynamic generation of ingres forms

12. Dynamic form generation with Developper 2000


 
Powered by phpBB® Forum Software