Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-16
20
Medium Priority
?
224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore 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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

660 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