How to use Function name in Stored procedure

How to create function in SQL-

USE [your database name]

/****** Object: UserDefinedFunction [dbo].[fnSplit] Script Date: 02.06.2015 4:02:02 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[fnSplit]

(

@sInputList VARCHAR(MAX) ,

@sDelimiter VARCHAR(MAX) = ','

)

RETURNS @List TABLE (item VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0

BEGIN

SELECT @sItem = RTRIM(

LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) -1))

@sInputList = RTRIM(

LTRIM(

SUBSTRING(

@sInputList,

CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter),

LEN(@sInputList)

)

IF LEN(@sItem) > 0

INSERT INTO @List

SELECT @sItem

END

IF LEN(@sInputList) > 0

INSERT INTO @List

SELECT @sInputList

RETURN

END

--SELECT * FROM [dbo].[fnSplit]('U,P,E,N,D,R,A',',')

How to use function-

select * from your_tablename where couserid in(select * from dbo.fnSplit('upendra,amit,ankur',','))

if helpful mark This is correct answer

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Upendra Pratap Shahi11y ago
1-
How to create function in SQL-
USE [your database name]
GO
/****** Object: UserDefinedFunction [dbo].[fnSplit] Script Date: 02.06.2015 4:02:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnSplit]
(
@sInputList VARCHAR(MAX) ,
@sDelimiter VARCHAR(MAX) = ','
)
RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN
SELECT @sItem = RTRIM(
LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) -1))
),
@sInputList = RTRIM(
LTRIM(
SUBSTRING(
@sInputList,
CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter),
LEN(@sInputList)
)
)
)
IF LEN(@sItem) > 0
INSERT INTO @List
SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List
SELECT @sInputList
RETURN
END
--SELECT * FROM [dbo].[fnSplit]('U,P,E,N,D,R,A',',')

2-
How to use function-
select * from your_tablename where couserid in(select * from dbo.fnSplit('upendra,amit,ankur',','))

if helpful mark This is correct answer
0
Shubham Kumar11y ago
Hi Deepak
just check the link you will get an idea how to use function in sp
http://www.c-sharpcorner.com/UploadFile/rohatash/calling-a-function-from-a-stored-procedure-in-sql-server-201/
+1