Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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

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
bluedragon99
Asked:
bluedragon99
  • 12
  • 5
  • 3
2 Solutions
 
momi_sabagCommented:
are you sure that dbo.fn_delimitedtostring exists in the same database?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
means that this user function does not exist in that database, or the user does not have permissions to it.
0
 
bluedragon99Author Commented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
momi_sabagCommented:
you wrote fn_delimitedtostring instead of fn_delimitedtotable
0
 
bluedragon99Author Commented:
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
 
bluedragon99Author Commented:
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
 
momi_sabagCommented:
well,
it seems like the function is doing some conversion and gets an error
0
 
bluedragon99Author Commented:
lol I noticed that.  Why is the function CheckFingerprint unable to work with varchar's in my table?  What is dimmed incorrectly?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
bluedragon99Author Commented:
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
 
bluedragon99Author Commented:
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
 
bluedragon99Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
bluedragon99Author Commented:
Can you tell me how to query/call it?  Looks very nice so far, thank you
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
bluedragon99Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
bluedragon99Author Commented:
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
 
bluedragon99Author Commented:
Got it!  

Changed end of function to:   where g.fingerprint=f.value
0
 
bluedragon99Author Commented:
Thanks angellll !!  you rock
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now