Is it possible to reference the Type of an table-field in a SP like pl/slqs %type ?

Question

Is it possible to reference the Type of an table-field in a SP like pl/slqs %type ?

peter greiff

Grasshopper

Points: 11
More actions
February 23, 2005 at 5:38 am

#68373

Hi there,

I have a big problem. I am writing a code generator for DML ops (insert update delete etc)
for different sql syntaxes. I was wondering if I can reference the datatype of a tablefield in a
stored procedure in transact sql:
normally you declare your variables like
my_variable INTEGER
but I need a easy way to use something like
my_variable [tablename].[rowname]???type

In Oracle (pl/sql) you can use <tablename>.<rowname>%TYPE

Please help me...

greetings peter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Charles Otten Default port Points: 1475 More actions · Answer 1

You can use the syscolumns table in the database in question to get the columns of a particular object. The type of column is determined by the xtype field. You'll need the object id to get the proper columns, but you can use the object_id([object name]) function to get the ID.

Hope this helps.

If the phone doesn't ring...It's me.

noeld SSC Guru Points: 96590 More actions · Answer 2

The short anwser is NO!

You could using dynamic SQL create some sort of hack to get around it but you will end up with an entangled code hard to maintain. If you stick to the use of tables only there is a posibility to handle it with select into with a negative where clause, but again is just another hack.

HTH

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

See if you can utilize this

USE NORTHWIND

GO

DECLARE @myCol VARCHAR(10)

SELECT

@myCol = DATA_TYPE

FROM

INFORMATION_SCHEMA.COLUMNS

WHERE

TABLE_NAME = 'Orders'

AND

COLUMN_NAME = 'OrderDate'

SELECT @myCol AS MySearchedDatatpye

MySearchedDatatpye

------------------

datetime

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 4

I don't think you are DECLARING any variable the way the poster requested.

when you write:

variable <tablename>.<rowname>%TYPE

in Oracle you are CREATING the variable with that datatype. So is not a matter of determining the datatype but to CREATE one!

HTH

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

I think I've mentioned, if the OP might utilize this. Hm, what about calling it a workaround? Or a start? Or...

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]