I have come up with a hack solution:
DECLARE @IDCOLUMN varchar(50)
DECLARE @OBJ varchar(50)
DECLARE @OBJID int
DECLARE @STATEMENT varchar(50)
SET @OBJ = 'PRODUCTS'
SET @OBJID = OBJECT_ID(@OBJ)
SET @IDCOLUMN = COL_NAME(@OBJID,1)
SELECT @IDCOLUMN As IDColumn
Given that the identity column is the first column in the table (which
most of my tables are), this will work (you can change this in the
COL_NAME function). It can be shortened to:
<cfset tablename = "PRODUCTS">
<CFQUERY NAME="qry1" DATASOURCE="dsn">
SELECT COL_NAME(OBJECT_ID('#tablename#'),1) As IDColumn
</CFQUERY>
This could be used in another query, such as:
<CFQUERY NAME="qry2" DATASOURCE="dns">
SELECT #qry1.IDColumn# As IDColumn
FROM #tablename#
</CFQUERY>
Thanks for all the input! If anyone knows a better way, please let me
know!
-----Original Message-----
From: Jeff Beer [mailto:[EMAIL PROTECTED]]
Sent: Sunday, February 17, 2002 11:42 AM
To: CF-Talk
Subject: RE: Dynamically Obtaining IDENTITY Column
Scott - do you mean the identity value of a newly inserted record, or to
select the ID value when you don't know the name of the identity column?
-----Original Message-----
From: Scott Van Vliet [mailto:[EMAIL PROTECTED]]
Sent: Sunday, February 17, 2002 2:22 PM
To: CF-Talk
Subject: Dynamically Obtaining IDENTITY Column
Hey All.
Does anyone out there know of a way to dynamically grab the identity
column from a table in a select statement?
Example:
<CFQUERY NAME="queryname" DATASOURCE="dsn">
SELECT @@IDENTITY_COLUMN
FROM TABLE_NAME
</CFQUERY>
Where "@@IDENTITY_COLUMN" would be the function to obtain the value of
the identity column.
TIA.
----
SCOTT VAN VLIET
BRD.WRKS INTERACTIVE
[EMAIL PROTECTED]
�
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
