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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL Server --> Faking out a field

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_XYZ

the query looks like
select
'A' as Type1,
isnull(x.B, 'n/a') as TypeValue1
from tblX_XYZ x

B, 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 strange
Where 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 columns

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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" columns
Kristen
Go to Top of Page
   

- Advertisement -