Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2005-11-11 : 07:29:57
|
David writes "I have a table that is created dynamically from a lookup table that is input by a user. Once the dynamic table is built, I have a stored proc that pulls from that table. I need he stored proc to test is a column exists. If the column does not exist, i need the column to be created in the recordset with a value of 'n/a'.The dynamic table is tblX_XYZthe query looks likeselect 'A' as Type1, isnull(x.B, 'n/a') as TypeValue1 from tblX_XYZ xB, may or may not exist in the table. If it does not exist, I need it to be in the recordset with a value of 'n/a'How do I create a query against the dynamic table.The table, since it is created dynamically, may contain fields a,b,c. The next time the table is created it may contain fields a,b,c,d,e.I need to write a query that selects a,b,c,d,e. If fields d and e are not in the table in this particular instance in which it[table] is being created, i need to have those fields in the recordset with a value of 'n/a'. The table is destroyed and recreated each time the query that builds it, runs.make sense?" |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-11 : 07:49:12
|
This seems strangeWhere do you want to show these data?If you use Front End application get the name of the columns from RecordSet and do this display there by checking the existing of the columnsMadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-11 : 09:06:16
|
Hi David , Welcome to SQL Team!IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'MyColumn')BEGIN PRINT 'Yes, the column exists'END Hopefully you can use that to influence how your dynamic SQL is constructed for "missing" columnsKristen |
|
|
|
|
|
|
|