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)
