I have written two functions exactly doing the same thing on two SQL tables containing some item information. Removing the unwanted characters and adding on the entries of the first table and creating a translation key as item.groups and on the second table item.class to the result set. My aim is to create a single function doing my job.
First table contain:
itemgroups
group_name
fertilizer1
fertilizer2
Second table
item
group_name | name
fertilizer1 | nitrogen
fertilizer1 | phosphorus
fertilizer2 | potassium
fertilizer2 |sodium
<pre><pre>ALTER FUNCTION [dbo].[new_translation_groups]
(
@column_name nvarchar(1000),
@group_name nvarchar(1000)
)
Returns nvarchar(1000)
As
BEGIN
DECLARE @result nvarchar(1000);
DECLARE @result2 nvarchar(1000);
SET @result = @column_name;
SET @result2 = @group_name;
SET @result2 = Replace(@result2, N' OR ', N'_');
SET @result2 = Replace(@result2, N' and ', N'_');
SET @result2 = Replace(@result2, N' ', N'_');
SET @result2 = Replace(@result2, N'/', N'_');
SET @result = Replace(@result, N' OR ', N'_');
SET @result = Replace(@result, N' and ', N'_');
SET @result = Replace(@result, N' ', N'_');
SET @result = Replace(@result, N'-', N'_');
WHILE @result Like N'%[_][_]%'
BEGIN
SET @result = Replace(@result, N'_', N'');
END;
WHILE @result2 Like N'%[_][_]%'
BEGIN
SET @result2 = Replace(@result, N'_', N'');
END;
RETURN N'item.class.'+LOWER(@result2)+ N'_'+ LOWER(@result);
END;
My second function
CREATE OR ALTER FUNCTION dbo.translationkey_v22
(
@column_name nvarchar(1000)
)
Returns nvarchar(1000)
As
BEGIN
DECLARE @result nvarchar(1000);
SET @result = @column_name;
SET @result = Replace(@result, N'and', N'_');
SET @result = Replace(@result, N' ', N'_');
WHILE @result Like N'%[_][_]%'
BEGIN
SET @result = Replace(@result, N'__', N'');
END;
Return N'item.group.' + LOWER(@result);
END;
What I have tried:
what i have tried
Alter FUNCTION [dbo].[new_translation_groups_newV1]
(
@column_name nvarchar(1000),
@group_name nvarchar(1000)
)
Returns nvarchar(1000)
As
BEGIN
DECLARE @result nvarchar(1000);
DECLARE @result2 nvarchar(1000);
DECLARE @result3 nvarchar(1000);
SET @result = @column_name;
SET @result2 = @group_name;
SET @result2 = Replace(@result2, N' OR ', N'_');
SET @result2 = Replace(@result2, N' and ', N'_');
SET @result2 = Replace(@result2, N' ', N'_');
SET @result2 = Replace(@result2, N'/', N'_');
SET @result = Replace(@result, N' OR ', N'_');
SET @result = Replace(@result, N' and ', N'_');
SET @result = Replace(@result, N' ', N'_');
SET @result = Replace(@result, N'-', N'_');
WHILE @result Like N'%[_][_]%'
BEGIN
SET @result = Replace(@result, N'_', N'');
END;
WHILE @result2 Like N'%[_][_]%'
BEGIN
SET @result2 = Replace(@result, N'_', N'');
END;
IF (@group_name = (SELECT group_name from itemGroups))
SET @result3 = N'item.group.' + LOWER(@result)
ELSE IF (@group_name = (SELECT group_name from item))
SET @result3= N'item.class.'+LOWER(@result2)+ N'_'+ LOWER(@result);
RETURN @result3
END;
But this is giving error as the query is selecting multiple rows