Hi Manjuke,  

Try to construct the string dynamically and execute it. SQL Server will expect a single value for @Param.

declare @Query as nvarchar(512), @spParam nvarchar(100)

Set @spParam='''s'',''t'''

set @Query = 'select * from sys.objects where type in ('+@spParam +')'

exec sp_executesql @Query

Having said this, i need to tell that dynamic sql is ugly.  It has a potential security threat through sql injection and can cause performance issues ona  busy server.

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!

Hi, I have to agree with Preethi about dynamic SQL.

Manjuke, you could try something like this if you need to avoid the dynamic SQL bit in your example:

Use a user-defined table-valued function that would break the string at the commas and return a table containing a single column of the values specified in @spParam This user defined function can be either a SQL function or a CLR function.

DECLARE @spParam nvarchar(50)
SET @spParam = '12,43,445'

SELECT * FROM <tablename> WHERE <fieldname> IN (SELECT * FROM fnX(@spParam))

Note: fnX is the function mention above.

Alternatively, you could embed the code to break the string at the commas within your stored procedure itself, like this:

DECLARE @spParam nvarchar(50)
SET @spParam = '123,5,43,445'

-- Declare a table variable to hold the IDs that are split from the parameter string
DECLARE @ParamTable TABLE
(
  [ParamValue] int
)

-- Split the parameter string, and populate the table variable with the IDs
WHILE (CHARINDEX(',', @spParam) > 0)
BEGIN

  INSERT INTO @ParamTable
  SELECT LEFT(@spParam, CHARINDEX(',', @spParam) -1)

  SET @spParam = RIGHT(@spParam, LEN(@spParam) - CHARINDEX(',', @spParam))

END

INSERT INTO @ParamTable
SELECT @spParam

SELECT * FROM <tablename> WHERE <fieldname> IN (SELECT [ParamValue] FROM @ParamTable)

---

Gogula G. Aryalingam (MVP - SQL Server)