Help! Can you pass arrays or tables into stored procedures?
-
any info would be helpful and appreciated.
-
You cannot use it directly as an array or table.
Workaround is cocatinate the values with usually '|' and split it in the stored procedure to avail it as a table.
Remihas posted a function which wil return a table when the concatinated string is passed.http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=188742#bm188765
Regards,
gova -
Hehe, looks like I made a fan here... Feels weird
. -
Okay I updated my post
Regards,
gova -
I didn't say I wans't ok with it
. -
You can pass a temporary table to a stored procedure. Just create the temp table and populate it before you call the sp, and drop it after you've executed it. That's how I always send set-based data to a sp. I prefer not to use the concatenation procedure described above because it adds to the complexity of both the client app and the sp, there is a size limitation of 8000 characters, and there is no type checking on the data arriving in the parameter so you have to validate the data while you're extracting it from the varchar parameter.
Brian
MCDBA, MCSE+I, Master CNE -
Or you can check the data before sending it to the proc...
-
I learned many years ago not to completely trust any other developer. A stored procedure could be called by another application written by another developer. It only costs a few extra cycles to validate the inputs to your procedure or function, and it eliminates finger-pointing.
Brian
MCDBA, MCSE+I, Master CNE -
I see. Thanx for the info.
-
The temporary table method, I believe, only works when calling from another stored procedure; not from an application via ADODB, etc. However, updating a table directly by modifying a returned recordset exposes access to the table. I suppose a workaround would be to use a dedicated data transfer table and have a stored procedure to transfer data from there to the 'real' table(s) but that seems horribly inefficient. Any other ideas?
-
Not exactly what he means.
I think he's refering to having a temp table (or dedicated table for this) where you can insert the ids that need to be processed. I think in this situation I would create a perm table with 2 fields (IdToProcess, UserId). That way everyone could be using this at the same time and you wouldn't have the overhead of creating/dropping the table all the time. The main advantage of this method is that you're not limited to 8000 characters and that you don't have to redo validation on the server for the datatype of the ids.
BTW temp tables are dropped when explicitly dropped or at the end of the connection (not the sql batch).
-
Remi,
To refine your comment on temp tables, my understanding is :
## are global to the connection
# are local to the stored procedure in which they are created.
I need to play with this stuff a bit to get an optimal solution.
-
## is global to ALL USERS
# is global to the connection (so the proc can run simultaneously with multiple users)
As I said I would opt for a perm table for a task like this. It costs nothing more to have it remain in the db once created (besides the normal usage of course). Temp tables are often a great help, but then can cause great problems too.
-
Remi,
Thanks for the correction - that could have been embarrassing!
-
I think you need to read today's article for embarassing
:
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply