3 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

Possible Duplicate:
Technique for sending lots of data into stored procTechnique for sending lots of data into stored proc

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?

Possible Duplicate:
Technique for sending lots of data into stored proc

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?

Possible Duplicate:
Technique for sending lots of data into stored proc

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?

    Post Locked by Jack Douglas
    Post Merged (source) to dba.stackexchange.com/questions/629/…
2 insert duplicate link
source | link

Possible Duplicate:
Technique for sending lots of data into stored proc

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?

Possible Duplicate:
Technique for sending lots of data into stored proc

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?

    Post Closed as "exact duplicate" by Jack Douglas of
    Tweeted twitter.com/#!/StackDBAs/status/126736038851911681
1
source | link

Passing array parameters to a stored procedure

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?