| Author |
Topic |
|
John T.
Posting Yak Master
112 Posts |
 Posted - 2003-04-21 : 12:10:18
|
| This forum is great for response. Nothing like it I have seen. Thanks for the help so far.If I want to pass an array from vb.net code to a stored procedure for execution in a query, is that allowable? Example is. I load an array in vb.net that contains the row number of a table and a certain type of selection. I want to compare all of the elements in the array with the db to make sure there are no duplications. I don't want to send one array value at a time to the sproc. Would like to send the entire array and then run through it in SQL to make the check.Thanks.John |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-21 : 12:17:13
|
Basically, no, SQL Server doesn't have an Array data type.There are some hacks around it though, involving CVS or fixed width values enclosed in varchars.If you can, go pick up Ken Henderson's book The Guru's Guide to Transact SQL. It has a great chapter on array imitation.Actually, it just rocks  Damian |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-04-21 : 13:00:38
|
| Also check out this very indepth article on this subject[url]http://www.algonet.se/~sommar/arrays-in-sql.html[/url]HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-21 : 13:11:19
|
| Excellent Book. I would also reccommend Admin911 for SQL Server.But I don't remeber reading ho to pass an array in? Also what type of array? Ho big?Maybe you can pass all the data together as 1 string and parse it on the SPROC...I have some code that I did that with somewhere, I'll try and dig it up.Brett8-) |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-04-21 : 13:37:15
|
| That's pretty neat, manufacturing arrays. Cool. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-04-21 : 13:48:13
|
quote:
But I don't remeber reading ho to pass an array in?
Ken's book includes xp_array.dll, an extended stored procedure that exports the fuctions xp_createarray,xp_setarray,xp_getarray,xp_destroyarray and xp_listarray HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-21 : 14:13:18
|
ok...I see xp_setarray and fn_set_array.But he still has to set the values of the array 1 at a time. And he's doing it in TRANSACT SQL, not externally.And there is a syntax I'm not familiar with:SELECT * FROM ::fn_Listarray(@hdl)What's the "::" for?I'm still trying to grasp what he's doing here (it's neat to build all these new things: arrays), but I still can't see the point between this and just using SQL.Also his comments up front are interesting:quote:
WARNING: Use these routines with care and at your own risk. Failing to deallocate an arraywill make it's memory unavailable to SQL Server
Brett8-)Edited by - x002548 on 04/21/2003 14:14:00 |
|
|
|
label
Posting Yak Master
197 Posts |
Posted - 2003-04-21 : 15:08:29
|
| I had a simliar problem here not to long ago. I had a field that I was searching on (the "Damage" field). Sometimes people would search for just one type of damage, other times they needed to be able to search for more than one type. My solution was to build that one part of the SQL statement that was doing the search for that component and pass that part of the SQL through a parameter to my Stored Proc and then do an exec on it with the code that was already there. So: exec('Select a, b, c, d from myTable where datediff(dd, prevDate, getdate())<90 and ' + @passedSql + ' order by a desc') |
|
|
|
|
Arrays with SQL 2000