June 3, 2009 at 6:25 am
Hello everyone,
Im getting msg 208, Level 16, state 1 error when I tried to execute the function below as
select * from Get_variation_List ('xxxx,yyy') or select * from [dbo].Get_variation_List ('xxxx,yyy').
Sp_help shows that ojbect do exists in the data base. Any help is much apprecoiated
USE XXX
go
set nocount on
print 'Script to create Get_variation_List Function'
print ' '
go
print 'Dropping existing Function'
if Object_Id('[Get_variation_List]') is not null
begin
drop function Get_variation_List
if @@error <> 0
begin
print 'ERROR dropping Function Get_variation_List'
end
else
begin
print 'Function Get_variation_List dropped'
end
end
go
CREATE function [dbo].[ Get_variation_List]
(@variation_name varchar(max)
)
returns
@VariationList table
(
variation_id int identity(1,1) not null,
variation_name varchar(40)
)
as begin
declare @Pos int,
@single_variation varchar(40)
--now extract each variation from @variation_name and insert into main table as one whole row
set @variation_name = LTRIM(RTRIM(@variation_name))
set @Pos = CHARINDEX(',', @variation_name, 1)
if REPLACE(@variation_name, ',', '') <> ''
begin
while @Pos > 0
begin
set @single_variation = LTRIM(RTRIM(LEFT(@variation_name, @Pos - 1)))
if @single_variation <> ''
begin
insert into @VariationList (variation_name)
values (@single_variation)
end
set @single_variation = RIGHT(@variation_name, LEN(@variation_name) - @Pos)
set @Pos = CHARINDEX(',', @variation_name, 1)
end
end -- end of main if loop
return
end
June 3, 2009 at 7:43 am
CREATE function [dbo].[ Get_variation_List]
There's an space character at the beginning of the function name. remove that space or just change your function call to select * from dbo.[ Get_variation_List] ('xxxx,yyy') adding and space before the Get_variation_List
June 3, 2009 at 7:52 am
Your reply solved my 3 hours head ache. I never looked at that bit of code ...
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply