| Author |
Topic |
|
amitgujrathi
Starting Member
17 Posts |
 Posted - 2004-05-27 : 17:26:04
|
| Does any one know :-- How to declare local variable in a function/stored procedure as the data type of a column of an existing table? (for example: it can be done in oracle as "declare p_local_var <tablename>.<Columnname>%Type"-- Is it at all possible in MS SQL Server 2000?Thanks in advance,Amit |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-27 : 17:28:57
|
| That isn't possible in SQL Server.Tara |
 |
|
|
amitgujrathi
Starting Member
17 Posts |
Posted - 2004-05-27 : 17:50:22
|
| Sorry about that, I just needed a fast answer & hence tried posting it twice.Thanks for the answer though !! |
|
|
|
amitgujrathi
Starting Member
17 Posts |
Posted - 2004-05-28 : 09:11:07
|
| Is there a workaround for declaring local variable in stored procedure as column data types? Let me know. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-28 : 12:25:53
|
| No there isn't. But why won't you know the data type when you are coding the stored procedure? You can get the information easily out of the INFORMATION_SCHEMA views.Tara |
|
|
|
amitgujrathi
Starting Member
17 Posts |
Posted - 2004-06-04 : 16:40:50
|
| I know I could easily get the information from INFORMATION_SCHEMA views & I also know the data types when writing the store procedure, but the constraint here is that I don't want to use the datatypes by specifing them directly.Please refer to my another query -- http://sqlteam.com/forums/topic.asp?TOPIC_ID=35898< I am trying to use that Creating Temp Table since I couldn't use this local declarations method>Also even if I get the information about the datatype from the INFORMATION_SCHEMA views, i cannot use it for declaring local variables.lets say something like this:DECLARE @ClientID GetColumnDataTypeFrom(<TableName>, <ColumnName>) {note: Here GetColumnDataTypeFrom(<TableName>, <ColumnName>) is a used defined function which returns the Datatype of a Column <ColumnName> from a Particular table <TableName>} |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 16:59:55
|
| So since you can't do this in SQL Server, we need to understand what you are trying to do. Not what you are trying to implement, but rather the business requirement. If we know that, then we could help come up with a solution for you.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-06-04 : 19:55:20
|
| unless this is homework ;)-ec |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-04 : 21:05:17
|
| You could declare the variables as sql_variant. That will hold any datatype other than text I believe. You would need to CAST them to do functions reserved for certain datatypes. |
|
|
|
amitgujrathi
Starting Member
17 Posts |
Posted - 2004-06-07 : 11:16:49
|
| 1. No this is not homework, I'm doing this for my work project.2. I did try using sql_variant, but like I said earlier I need to use the same datatypes as that of the columns of a table & if I have to do casting to get the actual datatypes, I would be again required to know the datatypes of the respective columns isn't it??3. The business requirement is that of doing less maintenance & making the store procedure so generalized, so that, if me being a consultant is gone, the people doing the maintenance/enhancements should not be required to do lot of changes to the store procedures, if a field/column is added/renamed/deleted and/or if the datatype is changed.4. I know if I use the exact datatypes available in SQL Server 2000 for the respective columns everything works fine (I've tested it all), but I have to specify all the columns (more than 200 for each of the 11 tables) names & their datatypes when inserting/updating as well as fetching using the OPENXML functionality & if a column is added/remaned/deleted and/or if the datatype is changed, some one has to update the store procedures & since the DB is going to sit remotely in clients network, it becomes more tedious, right?.5. Any one who has missed as to what I'm talking about please refer to the following link as well along with the current topic: http://sqlteam.com/forums/topic.asp?TOPIC_ID=35898Once Again, Thank you AllAmit |
|
|
|
|
declaring local variables as column data type