Sometimes we need to take multiple numbers/string paramter input in a single field of Crystal report.
So we can manage it like user will give all the number in a single field and we need to return as a table so that we can use it IN paramter.
Input: 2132,1231333,5555,321
Output:
2132
1231333
5555
321
-------------------------Function definition--------------------
CREATE FUNCTION [dbo].[StringToTable]
(
@psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
DECLARE @sTemp VARCHAR(10)
WHILE LEN(@psCSString) > 0
BEGIN
SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
LEN(@psCSString)))
SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
LEN(@psCSString)) + 1, LEN(@psCSString))
INSERT INTO @otTemp VALUES (@sTemp)
END
RETURN
END
--------------------Using this function into a select statment----------------
Declare @numbers varchar(50)
set @numbers = '2132,1231333,5555,321'
select * from tab where id in(select * from StringToTable(@numbers));
So we can manage it like user will give all the number in a single field and we need to return as a table so that we can use it IN paramter.
Input: 2132,1231333,5555,321
Output:
2132
1231333
5555
321
-------------------------Function definition--------------------
CREATE FUNCTION [dbo].[StringToTable]
(
@psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
DECLARE @sTemp VARCHAR(10)
WHILE LEN(@psCSString) > 0
BEGIN
SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
LEN(@psCSString)))
SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
LEN(@psCSString)) + 1, LEN(@psCSString))
INSERT INTO @otTemp VALUES (@sTemp)
END
RETURN
END
--------------------Using this function into a select statment----------------
Declare @numbers varchar(50)
set @numbers = '2132,1231333,5555,321'
select * from tab where id in(select * from StringToTable(@numbers));
Comments
Post a Comment