Stuff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stuff

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-01-02 : 22:20:52

Hi all, I try to create function based on sql below strikethrough allow to pass parameter:-

select STUFF((SELECT ',' + code
FROM tblEntity where EntityID in(~~1,3,4,5~~)
FOR XML PATH('')), 1, 1, '') AS CODES

My function
Alter Function dbo.FunEntity(@ID int)
returns Table
as Return
(
select STUFF((SELECT ',' + code
FROM tblEntity where EntityID in(@ID)
FOR XML PATH('')), 1, 1, '') AS CODES
)

select * from dbo.FunEntity(1,3,4)

Something wrong on the parameter declaration.

Please advise.

Thank you.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-01-03 : 02:48:57

Thank you.

Finally solved! Here is the solution:-

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

Alter Function [dbo].[FunEntity](@ID varchar(max))
-- select * from dbo.[FunEntity](3)
returns Table
as Return
(
select STUFF((SELECT ',' + code
FROM tblEntity where EntityID in(Select items from dbo.Split(@ID,','))
FOR XML PATH('')), 1, 1, '') AS CODES
)
GO

select * from dbo.FunEntity('1,3,4')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources