Table Value Function, Error

hi all,

  create function ufn_sample  (  	@name				varchar(20)  )  returns	table  as  return  (	  	declare @temp table  	(  		id				int identity(1,1),  		firstname		varchar(20)  	)    	declare @return varchar(5)    	insert into @temp values (@name)  	  	select *  		from @temp    );  go  

any help?

how fix error?

regards,

kumar

try

alter function ufn_sample ( 	@numberofcharacters	int, 	@name				varchar(20) ) returns	 table as  return  with remdups as ( 	select 			 			@name as firstname, 			cast(left(@name, 1) as varchar(max)) as firstnamewithoutdupes, 			1 as position 	 	union all  	select 			 			firstname, 			firstnamewithoutdupes + 									case 										when substring(firstname, position, 1) = substring(firstname, position + 1, 1) then '' 										when substring(firstname, position + 1, 1) not like '[a-z]' then '' 										else substring(firstname, position + 1, 1) 									end, 			position + 1 	from 		remdups 	where 		len(firstname) > position )  ,dupsseqs as ( 	select 			firstnamewithoutdupes, 			row_number() over (order by position desc) as rn 	from 		remdups )  ,cte as ( 	select 			upper(firstnamewithoutdupes) as firstname 		from 			dupsseqs 		where 			rn = 1 )  ,cte1 as ( 	select 			 			firstname, 			cast(case 					when len(firstname) <= @numberofcharacters then firstname 					else left(firstname, 1) 				end as varchar(max)) as partedfirstname, 				1 as position 		from 			cte  	union all  	select 			 			c.firstname, 			case 				when len(firstname) <= @numberofcharacters then firstname  				else partedfirstname +  				case		 					when substring(firstname, position + 1, 1) in ('a','e','i','o','u') 						and len(partedfirstname) + len(firstname) - position > @numberofcharacters then '' 					else substring(firstname, position + 1, 1) 				end 			end, 			position + 1			 	from 		cte1 c 	where 		len(firstname) > position ), cte2 as (	 	select 			*, 			row_number() over (order by position desc) as rn 	from 		cte1 )  select left(partedfirstname + '*****', @numberofcharacters) as fixedname from 	cte2 where 	rn = 1    go   drop table #temp1 create table #temp1 ( 	fname		varchar(20), 	lname		varchar(20) )  insert into #temp1 values ('amit', 'gonzalez') insert into #temp1 values ('ralph', 'johnson') insert into #temp1 values ('theo', 'johns')  select 		fname, 		f1.fixedname as new_first_name, 		lname, 		f2.fixedname as new_last_name 	from  		#temp1 		cross apply dbo.ufn_sample(5,fname) f1 		cross apply dbo.ufn_sample(5,lname) f2  

 

although suggest switch order of parameters in function. took me while figure out why function failing (because tried call name,5 , not 5, name) 

 

---

premature optimization root of evil in programming. (c) donald knuth

naomi nosonovsky, sr. programmer-analyst

my blog

SQL Server  >  Transact-SQL