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.
| Author |
Topic |
|
sdiwi
Starting Member
27 Posts |
 Posted - 2005-09-14 : 10:49:51
|
| Hello.I just wondered if it is possible to write a function (maybe also a stored procedure) that can be feeded with something like an array or a TABLE datatype.you know, sth like:CREATE FUNCTION dbo.test( @codeVars TABLE(d_ID INT),@str VARCHAR(255))RETURNS ......END(of course i tried the code above - and of course it did not work...)now i try to solve it with CSV parameters and split them into a table that can be returned or sth. but a direct way would be better.hope you understand what i mean and can give me some help,peace to the world,bye,sdiwi. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-14 : 11:18:21
|
| You might be better server by explaining what you are trying to do...abstract concepts as described are very hard for us to solve.Can you post DDL, sample input data and sample (matching) expected results? |
 |
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-09-14 : 11:31:28
|
| i just want to know wheter i can pass a TABLE into an udf or a stored procedure. i really don't know what for i should describe anything. sorry, but for me this is a clear question - but maybe you messed something up, or i just didn't make myself clear.if this does not work, i will code a workaround. byby... |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-09-14 : 11:31:37
|
| Sadly no, you can't do either. Arrays do not exist in SQL Server and BOL states (if you look up "Create Function"): scalar_parameter_data_type Is the parameter data type. All scalar data types, including bigint and sql_variant, can be used as a parameter for user-defined functions. The timestamp data type and user-defined data types not supported. Nonscalar types such as cursor and table cannot be specified.One alternative would be to create an inline xml document and pass it into the function as text. Then you can use OPENXML to put it into a table. However if you're going to jump through these types of hoops, you may as well just make it a csv.Mike"oh, that monkey is going to pay" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-14 : 11:38:57
|
| But how would you use this udf with an array?Why not just use a table and a stored procedure. Conceptually they are the same.I can't see through to how you would use that udf in a SELECT clauseBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-15 : 08:43:57
|
| "....really don't know what for i should describe anything".If you describe your problem in detail, maybe we can come up with a solution that means you don't have to go down this route....which as stated looks to be a dead-end.You started from the presumption that you needed to pass a table into a SP/UDF...I'm trying to challenge you to re-examine that premise, by detailing to us your business requirements and maybe we could spot a simpler solution. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-18 : 22:46:10
|
alternatively, instead of passing the table in the UDF, you can save those values in a staging table, then refer to that table for your UDF or SP,another approach is that if your table only has one-column, pass the values as a delimited string and use a parser posted somewhere here, i think Kristen created a sticky for it  HTH--------------------keeping it simple... |
|
|
|
|
|
|
|
|
UDF with multidimensional parameters