October 20, 2004 at 9:29 am
Hi All,
I need to query a Column of a table based on its ordinal position from a single select statement
Here is my select statement
Select col_name(object_id('TABLENAME'),13) from TABLENAME.The result is obivious that it returns the actual columnname.
Question is How to query the column that is returned from the function col_name in one select statement.
October 20, 2004 at 10:15 am
Most likely, you will need a dynamic query; retrieve the column name from the query, then do
exec 'select ' + @Column + ' from TABLENAME'
I can't think of anyway other than that.....
October 20, 2004 at 10:28 am
Thanks for your suggestion in a very timely manner.
But is there any alternative way to query a table where in column may Or may not exist, without throwing an error(Invalid column name).
October 21, 2004 at 7:02 am
You could do something like this:
DECLARE @tblname sysname, @colname sysname
SET @tblname = '<table to check>'
SET @colname = '<column to check>'
IF (SELECT COUNT(*)
FROM sysobjects t JOIN syscolumns c ON t.id = c.id
WHERE t.name = @tblname
AND c.name = @colname
) > 0
BEGIN
-- column exists
END
Alternatively, you could create a UDF to do this:
GO
CREATE FUNCTION dbo.fColExists
(
@tblname sysname,
@colname sysname
)
RETURNS tinyint
AS
BEGIN
DECLARE @rcode tinyint
IF (SELECT COUNT(*)
FROM sysobjects t JOIN syscolumns c ON t.id = c.id
WHERE t.name = @tblname
AND c.name = @colname
) > 0
SET @rcode = 1 -- column exists
ELSE
SET @rcode = 0 -- column does not exist
RETURN @rcode
END
GO
--- TEST the UDF
DECLARE @tblname sysname, @colname sysname
SET @tblname = '<table to check>'
SET @colname = '<column to check>'
IF dbo.fColExists(@tblname, @colname) > 0
PRINT 'Exists'
ELSE
PRINT 'Column ' + @tblname + '.' + @colname + ' not found.'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply