declare @Symbol varchar(20)
Declare @Sql varchar(8000)
declare @apos char(1) 
set @apos=''''

set @Symbol = @apos +'A' + @apos + ',' + @apos + 'B' + @apos

set @Sql = 'Delete from ts_imported where c_change_symbol in (' + @symbol + ')'

Execute(@Sql)

quote:

---

Originally posted by harsh_athalye

use dynamic sql as below:

declare @Symbol varchar(20)
Declare @Sql varchar(8000)
declare @apos char(1) 
set @apos=''''

set @Symbol = @apos +'A' + @apos + ',' + @apos + 'B' + @apos

set @Sql = 'Delete from ts_imported where c_change_symbol in (' + @symbol + ')'

Execute(@Sql)

Harsh Athalye
India.
"Nothing is Impossible"

---

quote:

---

Dynamic SQL Works well.

---

quote:

---

The good ones:

* The iterative method. Looping through a comma-separated list, and returning the elements in a table, either with a used-defined function (UDF) or a stored procedure. Decent performance, and easy to understand and easily extensible.

* Using a table of numbers to unpack a comma-separated list, either in a UDF or an SP. The fastest of all methods for a list with delimiters.

* Fixed-length array. Rather than using a comma-separated list, use a string where all elements have the same length. You unpack the string with a table of numbers in a UDF or SP. This is the fastest method I know of.

* XML. Overkill for a comma-separated list, but marvellous for an array of structured data. Only possible on SQL 2000. Decent performance, but slower than the iterative method.

The ones to stay away from:

* Dynamic SQL. For a list of numbers, it may appear simpler than any other method, but there are permissions issues to consider. And messier to use for a list of strings. Performance is unacceptable for long input. It is probably the only viable method on SQL 6.5 though.

* Making the List Into a SELECT. Transform a delimited list into an INSERT statement. Or many INSERT statements. Cute, but cannot handle input longer than ~3-5000 chars, and has no other particular benefit.

* Really slow methods. Methods that uses charindex, patindex or LIKE. These solutions are just unbelievably slow even for short input.

---

quote:

---

Dynamic SQL has its limitations, and is not something I would recommend. For starters, notice the "GRANT EXEC" command in the above script. That statement grants EXECUTE permission to the user WebUser. But that is not enough for WebUser to execute this stored procedure. The user executing dynamic SQL commands needs explicit permissions on the underlying tables, which is not something I would do on a production system. Because of this limitation, I added a "GRANT SELECT" command in the above script, to enable WebUser to run the stored procedure.

---