October 27, 2005 at 8:41 am
hi!
is it possible?
I made a function that return ids in rows like:
-----
14
0
-1
-2
then. I want to make another function that grab this result table and join with another table to bring me the id's descriptions like:
------------------
14 | Administrator
0 | Host
-1 | Users
-2 | All Users
so. is there any way for I do this:
@result = split()
getnames( @result )
[]s
October 27, 2005 at 9:18 am
Please post additional Table definition information and the logic for your function.
I am guessing here, but if your function is a table valued function.
Example
CREATE FUNCTION myfunction ( @Myvar int)
RETURNS TABLE
....
Then you can just join the results as if it were a table/derivedtable/or view.
Select description
From MydescriptionTable a
Join dbo.myfunction b on a.ID = b.id
October 27, 2005 at 11:08 am
split function that generate the first table result:
create FUNCTION Split( @input VARCHAR(1000), @delimiter CHAR(1) = '|' )
RETURNS @output TABLE( valor VARCHAR(1000) )
AS
BEGIN
DECLARE @join VARCHAR(1000)
DECLARE @result VARCHAR(1000)
DECLARE @valor VARCHAR(1000)
WHILE( 1 = 1 )
BEGIN
--ver se o delimitador existe na string
IF charindex( @delimiter, @input ) = 0
BEGIN
--se não existir o delimitador, insere somente a string na tabela de saída
IF( @input '' )
BEGIN
INSERT INTO @output ( valor ) VALUES ( @input )
END
BREAK
END
ELSE
BEGIN
SET @valor = substring( @input, 1, charindex( @delimiter, @input ) -1 )
IF( @valor '' )
BEGIN
INSERT INTO @output ( valor ) VALUES( @valor )
SET @input = SUBSTRING( @input, CHARINDEX( @delimiter, @input )+ 1, LEN( @input ) )
END
END
END
RETURN
END
-- SELECT TOP 10 valor FROM Split( '14;0;-1;-2;', ';' )
-----------------------
14 Administrators
0 Administrators
-1 All Users
-2 Host
function that I want to change. it expects 1 single id to return it description. Now I want to make a similar function that receives a table of ids, and returns a table of results
ALTER FUNCTION Roleid2Rolename( @id VARCHAR(1000) )
RETURNS @output TABLE( description VARCHAR(1000) )
AS
BEGIN
DECLARE @result VARCHAR(1000)
SELECT @result =
( SELECT DISTINCT( r.rolename ) FROM userroles ur, roles r
WHERE r.roleid = ur.roleid
AND ur.roleid = CAST( @id AS VARCHAR ) )
IF( @id = '0' )
SET @result = 'Administrators'
IF( @id = '-1' )
SET @result = 'All Users'
IF( @id = '-2' )
SET @result = 'Host'
INSERT INTO @output ( description ) VALUES ( @result )
RETURN
END
/*
select description from Roleid2Rolename( '0' ) UNION
select description from Roleid2Rolename( '12' ) UNION
select description from Roleid2Rolename( '-2' ) UNION
select description from Roleid2Rolename( '-1' )
*/
description
--------------
Administrators
All Users
Host
usuarios
Split2Table used to do both, split values, and do the select joins and return the descriptions as above:
ALTER FUNCTION Split2Table( @input VARCHAR(1000), @delimiter CHAR(1) = '|' )
RETURNS @output TABLE( id VARCHAR(1000), description VARCHAR(1000) )
AS
BEGIN
DECLARE @description VARCHAR(1000)
DECLARE @join VARCHAR(1000)
DECLARE @result VARCHAR(1000)
DECLARE @valor VARCHAR(1000)
WHILE( 1 = 1 )
BEGIN
--ver se o delimitador existe na string
IF charindex( @delimiter, @input ) = 0
BEGIN
--se não existir o delimitador, insere somente a string na tabela de saída
IF( @input '' )
BEGIN
SELECT @description = ( SELECT description FROM Roleid2Rolename( @input ) )
INSERT INTO @output ( id ) VALUES ( @result )
END
BREAK
END
ELSE
BEGIN
DECLARE @id VARCHAR(1000)
SET @id = substring( @input, 1, charindex( @delimiter, @input ) -1 )
IF( @id '' )
BEGIN
SELECT @description = ( SELECT description FROM Roleid2Rolename( @id ) )
INSERT INTO @output ( id, description ) VALUES( @id, @description )
SET @input = SUBSTRING( @input, CHARINDEX( @delimiter, @input )+ 1, LEN( @input ) )
END
END
END
RETURN
END
-- SELECT top 10 id, description FROM Split2Table( '14;0;-1;-2;', ';' )
id description
----------------------
14 Administrators
0 Administrators
-1 All Users
-2 Host
Now I made Split() to just split the values, and I need to make a function to receive the @results table from Split() and then, do the select joins. And the result of It,I pass to the function above:
ALTER FUNCTION IDList2DescList( @IDList VARCHAR(1000), @Delimiter CHAR(1) )
RETURNS VARCHAR(1000)
AS BEGIN
DECLARE @result VARCHAR(1000)
SELECT @result = ISNULL( @result + ' - ', '' ) + description FROM Split2Table(@IDList, @Delimiter )
RETURN @result
END
--SELECT dbo.IDList2DescList( '14;0;-1;-2;', ';' )
--------------------------------------------------
Administrators - Administrators - All Users - Host
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply