[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Create SQL Funtion

Hello I need to turn the following query into a UDF.

There will be one accepting parameter, @control_account.

The return value should be the string
select rtrim(substring(isnull((select '-'+vchr_segment_descr from #t t1 for xml path('')),' '),2,2000))

Thanks.
-- incoming parameter
declare @incoming_string varchar(255)
set @incoming_string = '4-8-6'

-- counter
declare @counter int
set @counter = 1

While @counter < dbo.ufn_CountChar(@incoming_string,'-') + 2
Begin

-- Step 1: Set the string
declare @string varchar(255)
If @counter = 1 BEGIN
set @string = @incoming_string
END 

If @counter > 1 Begin
set @string = @string
End

-- Step 2: Set the start point
declare @start int
set @start = 1

-- Step 3: Find the position of the first "-" in the string less 1 position
declare @break int
if dbo.ufn_countChar(@string,'-') = 0 Begin
set @break = len(@string) End

if dbo.ufn_countchar(@string,'-') > 0 Begin
set @break = charindex('-',@string,1) - 1 End

-- Step 4: Parse out the string
Insert into #t
select  
        vchr_Segment_Descr
from
        #tbl_Control_Accounts_Detail
Where
        int_CAD_Record_Number = left(@string,@break)


-- Step 5: Set the new String
set @string = substring(@string,@break+2,len(@string))


set @counter = @counter + 1

End
--print @string
select rtrim(substring(isnull((select '-'+vchr_segment_descr from #t t1 for xml path('')),' '),2,2000))


drop table #t
drop table #tbl_Control_Accounts_Detail

Open in new window

0
gdspeare
Asked:
gdspeare
1 Solution
 
igni7eCommented:
CREATE PROCEDURE sp_GetInventory
@control_account varchar(10)
AS

...your code...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
functions wont be able to access the temptables, so it is not possible unless you change the logic or pass the table as another parameter
0
 
SharathData EngineerCommented:
let us know if tbl_Control_Accounts_Detail is your actual table name. If yes you can create a function to pass the input string string like '4-7-2' and get the corresponding values from the table.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
SharathData EngineerCommented:
If you have table like tbl_Control_Accounts_Detail in your database, create the attached function.

Execute the function like this.

select dbo.fun_values('4-7-4')
output: South-New Braunfels-South
create function fun_values(@incoming_string as nvarchar(500))
returns nvarchar(2000)
begin
declare @return nvarchar(2000)
;with CTE as (
select *
  from (
		select substring('-' + incoming_string, n, 1) as incoming_string,n
		  from (select @incoming_string as incoming_string) as t1
		 cross join (select number from master..spt_values where type = 'P') as numbers(n)
		 where substring('-' + incoming_string, n, 1) not in ('','-')) as t1
  join tbl_Control_Accounts_Detail as t2
    on t1.incoming_string = t2.int_CAD_Record_Number)
select @return = rtrim(substring(isnull((select '-'+vchr_Segment_Descr 
  from CTE for xml path('')),' '),2,2000))
return @return
end

Open in new window

0
 
gdspeareAuthor Commented:
Final Result.

Thanks for the guidance.
USE [db_Budget]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_Control_Account_Alpha]    Script Date: 12/31/2009 15:35:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[ufn_Control_Account_Alpha](
@incoming_string varchar(255)
)
Returns varchar(255)

AS

Begin




declare @t table (vchr_segment_descr varchar(255))

-- incoming parameter
--declare @incoming_string varchar(255)
--set @incoming_string = '4-8-6'

-- counter
declare @counter int
set @counter = 1

While @counter < dbo.ufn_CountChar(@incoming_string,'-') + 2
Begin

-- Step 1: Set the string
declare @string varchar(255)
If @counter = 1 BEGIN
set @string = @incoming_string
END 

If @counter > 1 Begin
set @string = @string
End

-- Step 2: Set the start point
declare @start int
set @start = 1

-- Step 3: Find the position of the first "-" in the string less 1 position
declare @break int
if dbo.ufn_countChar(@string,'-') = 0 Begin
set @break = len(@string) End

if dbo.ufn_countchar(@string,'-') > 0 Begin
set @break = charindex('-',@string,1) - 1 End

-- Step 4: Parse out the string
Insert into @t
select  
        vchr_Segment_Descr
from
        tbl_Control_Accounts_Detail
Where
        int_CAD_Record_Number = left(@string,@break)


-- Step 5: Set the new String
set @string = substring(@string,@break+2,len(@string))


set @counter = @counter + 1

End

declare @control_account_alpha varchar(255)
set @control_account_alpha = 
		(select rtrim(
			substring(
				isnull(
					(select '-'+vchr_segment_descr from @t t1 for xml path('')
					)
					,' '
					),2,2000)
					)
		)


Return @control_account_alpha 
End

Open in new window

0
 
SharathData EngineerCommented:
Hello gdspeare, did you check my query?  I did not use the loop instead I use spt_values table.

Why do you want to declare a table in side the function? You can directly use the same table name.

Check my query and let me know your concerns.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now