Solved

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

Posted on 2008-10-16
20
216 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 142

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
 
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 142

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 142

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 142

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 142

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now