Solved

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

Posted on 2008-10-16
20
219 Views
Last Modified: 2010-04-21
I have the SQL procedure below but the am getting object not found on dbo.fn_delimitedtostring

Any Ideas?
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 = g.Fingerprint
where g.fingerprint=0
go

Open in new window

0
Comment
Question by:bluedragon99
  • 12
  • 5
  • 3
20 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22731277
are you sure that dbo.fn_delimitedtostring exists in the same database?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22731283
means that this user function does not exist in that database, or the user does not have permissions to it.
0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22731490
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

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22731569
you wrote fn_delimitedtostring instead of fn_delimitedtotable
0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22731635
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..
0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22731816
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.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22731942
well,
it seems like the function is doing some conversion and gets an error
0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22732173
lol I noticed that.  Why is the function CheckFingerprint unable to work with varchar's in my table?  What is dimmed incorrectly?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22732195
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

Open in new window

0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22732405
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...
0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22732882
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

Open in new window

0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22732938
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

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22733204
this one works:
create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Open in new window

0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22733209
Can you tell me how to query/call it?  Looks very nice so far, thank you
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22733218
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'

Open in new window

0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22733401
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'.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22733811
aaaarahg..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CheckFingerprint] @FingerString nvarchar(max), @delimiter nvarchar(32) 
 
as
set nocount on
select f.Value from dbo.ParmsToList(@FingerString, @delimiter) f
left outer join TheGoodTable g
on f.Value= cast(g.Fingerprint as varchar(100))
where g.fingerprint='0'

Open in new window

0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22733868
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?
0
 
LVL 1

Author Comment

by:bluedragon99
ID: 22733896
Got it!  

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

Author Closing Comment

by:bluedragon99
ID: 31506752
Thanks angellll !!  you rock
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question