Converting a stringbuilder object to a stored procedure

[Paul Johnson]

Hi,

I have a bit of a problem with a stringbuilder and converting it so that I can use it as a stored proc

The original code goes like this

The initial part of the stringbuilder is simple enough - it is something like

Dim foo as New StringBuilder() = String.Empty
foo = string.format("DELETE dog FROM animal WHERE name='{0}'", winform.AnimalName.ToString())
 

the next part then checks if it's a multiple selection. If it's not, ";" is appended to it.
If there is more than one selection, " OR name='{0}'" is added onto the end to build up the SQL command which is then executed. There is no limit to the number of " OR name='{0}'" that can be added

The problem is that I'd like to change this into a stored procedure. A single instance is easy enough, it's the second one which is messing me about.

Is there a simple way to convert this form of concatenation and converting it to a stored procedure?

Thanks

[ck9663]

Yes, there is a way. You still have to build a string to consolidate all those values that needs to be deleted. Pass it to an SP with a single variable. Inside the SP, use the IN function instead of OR operator.

The catch: the variable might not be big enough to accommodate the entire value.

Good Luck!!!

~~ CK