MS SQL Server, Dynamic SQL generation in T-SQL

Dynamic SQL generation in T-SQL

Author	Message
GBB #1 / 5	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

BPMargoli #2 / 5	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

Tom Jone #3 / 5	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

GBB #4 / 5	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

GBB #5 / 5	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. - Hide quoted text - - 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

Page 1 of 1

[ 5 post ]