Reputation: 189
I need to call a stored procedure like this:
exec myProcedure '(5, 15, 45)'
And inside the procedure I need to use the int-set within an in-Clasue like this:
SELECT ... FROM table WHERE (days in @intSet)
This always bring a syntax Error in the WHERE-Clause. (SqlServer evidently does not allow to simply replace a part of the statement by a string)
I can easily split the integer values of the string into several integers using CHARINDEX, SUBSTRING, RTRIM, LTRIM, CONVERT - this is not an issue - it works fine.
any ideas are greatly appreciated
Gerald
Upvotes: 2
Views: 554
Reputation: 69524
You will need to create a split function inside you database,
CREATE FUNCTION [dbo].[split]
(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
)
RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
Then you would use this split function insdie your Procedure to split the values to be used with In operator.
CREATE PROCEDURE GetData
@intSet VARCHAR(1000) = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TableName
WHERE days IN (SELECT Val FROM dbo.split(@intSet ))
END
Upvotes: 4