Solved

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

Posted on 2008-10-16
20
217 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to extract a "coded date" from a string field? 4 54
How can I get this column in my query? 2 40
Error when saving to sql table a '/' 5 26
CROSS APPLY 4 45
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

18 Experts available now in Live!

Get 1:1 Help Now