Stored procedure not working properly dbo.fn_delimitedtostring "object not found" error

are you sure that dbo.fn_delimitedtostring exists in the same database?

means that this user function does not exist in that database, or the user does not have permissions to it.

BrandonGalderisi set me up with the function, which looks perfect except for that missing function..

if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Select allOpen in new window

you wrote fn_delimitedtostring instead of fn_delimitedtotable

correct, ok got those in there now i'm getting:

Msg 245, Level 16, State 1, Procedure CheckFingerprint, Line 5
Conversion failed when converting the varchar value '9A53262B57393E025238677B51879899' to data type int.


Query:

exec CheckFingerprint @FingerString = '44D88612FEA8A8F36DE82E1278ABB02F,44D88612FEA8A8F36DE82E1278ABB02F' , @delimiter = ','


Something must be dimmed wrong..

apparently this line is the problem...


select theValue from dbo.fn_delimitedtostring(@FingerString, @delimiter) f


Msg 245, Level 16, State 1, Procedure CheckFingerprint, Line 5
Conversion failed when converting the varchar value '9A53262B57393E025238677B51879899' to data type int.

well,
it seems like the function is doing some conversion and gets an error

lol I noticed that.  Why is the function CheckFingerprint unable to work with varchar's in my table?  What is dimmed incorrectly?

you might try this:

create procedure CheckFingerprint @FingerString nvarchar(max), @delimiter nvarchar(32) 
-- name it what you want obviously.  
as
set nocount on
select theValue from dbo.fn_delimitedtostring(@FingerString, @delimiter) f
left outer join TheGoodTable g
on f.theValue = cast(g.Fingerprint as varchar(100))
where g.fingerprint=0
go

Select allOpen in new window

looks good but I can't win, now when I go to new stored procedure and save it, it doesn't show up under stored procedures...

Still not working...


Msg 245, Level 16, State 1, Procedure CheckFingerprint, Line 5
Conversion failed when converting the varchar value 'a' to data type int.


exec CheckFingerprint @FingerString = 'a,b' , @delimiter = ','


Table contains:

a
b
c
1
2
3
4

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CheckFingerprint] @FingerString nvarchar(max), @delimiter nvarchar(32) 
 
as
set nocount on
select theValue from dbo.fn_delimitedtostring(@FingerString, @delimiter) f
left outer join TheGoodTable g
on f.theValue = cast(g.Fingerprint as varchar(100))
where g.fingerprint=0

Select allOpen in new window

If anybody can write a function that takes delimited values and returns which of those are contained in the table then that'll do it, shouldn't be that hard, I'm just not a SQL expert

Can you tell me how to query/call it?  Looks very nice so far, thank you

making your code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CheckFingerprint] @FingerString nvarchar(max), @delimiter nvarchar(32) 
 
as
set nocount on
select theValue from dbo.ParmsToList(@FingerString, @delimiter) f
left outer join TheGoodTable g
on f.theValue = cast(g.Fingerprint as varchar(100))
where g.fingerprint='0'

Select allOpen in new window

Msg 207, Level 16, State 1, Procedure CheckFingerprint, Line 7
Invalid column name 'theValue'.
Msg 207, Level 16, State 1, Procedure CheckFingerprint, Line 5
Invalid column name 'theValue'.

ok functions entered now but it's not returning anything

exec CheckFingerprint @FingerString = '1 2 3' , @delimiter = ' '


Returns successfully but there are no values in the Results window

TheGoodTable looks like:

Fingerprint
1
2
3
4


So I would assume it should have returned 1 2 3 or 1,2,3?

Got it!  

Changed end of function to:   where g.fingerprint=f.value

Thanks angellll !!  you rock