Hey I have a table like this:
ID BitOne BitTwo BitThree
-- ------ ------ --------
99 NULL 1 NULL
99 1 NULL NULL
And I need a user function that returns the string "BitTwo, BitOne" for the ID: 99 - which would be the input parameter for the function.
Is that possible?
Use [SOFDB1]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec SELECT dbo.GetString(7)
ALTER FUNCTION dbo.GetString
(
@ID BIGINT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @RESULT_STRING nvarchar(max);
SELECT
@RESULT_STRING = CONVERT(nvarchar,(CASE
WHEN t.BitOne IS NOT NULL THEN CONVERT(varchar,t.BitOne)
ELSE 'NULL'
END) + ',' +
(CASE
WHEN t.BitTwo IS NOT NULL THEN CONVERT(varchar,t.BitTwo)
ELSE 'NULL'
END) + ',' +
(CASE
WHEN t.BitThree IS NOT NULL THEN CONVERT(varchar, t.BitThree)
ELSE 'NULL'
END)) FROM TestTable1 t WHERE t.ID = @ID
RETURN @RESULT_STRING
END
and call
in sp or something to get the value of that function >>>> SELECT dbo.GetString( theIDtoPass )
Function:
CREATE FUNCTION getList (
@id_parameter INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @result VARCHAR(MAX)
SELECT @result = MAX(CASE WHEN t.bitone = 1 THEN 'bitone' ELSE '' END) +','+
MAX(CASE WHEN t.bittwo = 1 THEN 'bittwo' ELSE '' END)
FROM your_table t
WHERE t.id = @id_parameter
GROUP BY t.id
RETURN @result
END
GO
Use:
SELECT MAX(CASE WHEN t.bitone = 1 THEN 'bitone' ELSE '' END) +','+
MAX(CASE WHEN t.bittwo = 1 THEN 'bittwo' ELSE '' END)
FROM TABLE t
WHERE t.id = @id_parameter
GROUP BY t.id
But it'd help to know how you want to handle when an id value doesn't have a value for bitone, bittwo, etc. If both are NULL or not 1, then you'll still get a comma back as a result.
Well created a table with 4 fields ID - BigInt
BitOne - BigInt
BitTwo - BigInt
BitThree - BigInt
Sample data
1 1 NULL 1 >>> Output '1,NULL,1'
2 1 NULL NULL >>> Output '1,NULL,NULL'
3 NULL 1 NULL >>> Output 'NULL,1,NULL'
4 NULL 1 1 >>> Output 'NULL,1,1'
5 1 1 1 >>> Output '1,1,1'
QUERY >>>>>
SELECT
CONVERT(nvarchar,(CASE
WHEN t.BitOne IS NOT NULL THEN CONVERT(varchar,t.BitOne)
ELSE 'NULL'
END) + ',' +
(CASE
WHEN t.BitTwo IS NOT NULL THEN CONVERT(varchar,t.BitTwo)
ELSE 'NULL'
END) + ',' +
(CASE
WHEN t.BitThree IS NOT NULL THEN CONVERT(varchar, t.BitThree)
ELSE 'NULL'
END)) AS RESULTSTRING from TestTable1 t WHERE t.ID = @ID
You can replace 'NULL' with '' to make empty string there or can put any string
Hope that helps
Regards, J'Sinh
Please try this solution.
DECLARE @Colors VARCHAR(MAX)
SELECT @Colors = ISNULL(@Colors + ', ', '') + cmnColor.Name
FROM [dbo].[CMN_Sel_Colors] as cmnColor
INNER JOIN [dbo].[Sel_ItemColor] AS itemcolor ON itemcolor.ColorID = cmnColor.ColorID
WHERE itemcolor.ItemID = @ItemID
RETURN @Colors
