Gerald Trost
Gerald Trost

Reputation: 189

how to pass an int-set to sqlserver in a string to stored procedure and use it in an IN-Clause

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 plan a solution like this:

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.

But I still cannot figure out into what kind of variable could I then put those integers in order to later use it with the IN-Clause ?

any ideas are greatly appreciated

Gerald

Upvotes: 2

Views: 554

Answers (1)

M.Ali
M.Ali

Reputation: 69524

You will need to create a split function inside you database,

Definition Of Split Function

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

Stored Procedure

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

Related Questions