October 27, 2005 at 10:25 am
Luciana...
Here is a pretty small solution for you. Please implement something like that and forget all aobut cursors and d-sql. You don´t need it. TSQL is great and nice. Also is powerful, but tou need a bit of time to get used to it..
Take care on validations and replace table and column names for your own.
Good luck,
Nicolas
CREATE TABLE tmp_Roles ( Id_Role INT identity, RoleName VARCHAR(50))
GO
CREATE TABLE tmp_TestingTable (Id_Test INT identity, Roles VARCHAR(50))
GO
CREATE FUNCTION GetRole( @RoleId INT)
RETURNS VARCHAR(50)
AS BEGIN
DECLARE @Result VARCHAR(50)
SELECT @Result = RoleName FROM tmp_Roles WHERE Id_Role = @RoleID
RETURN @Result
END
GO
CREATE FUNCTION GetRoles( @RolesID VARCHAR(50), @Delimiter CHAR(1) )
RETURNS VARCHAR(1000)
AS BEGIN
DECLARE @Result VARCHAR(1000),
@CurrentRole INT,
@Start INT, @End INT
DECLARE @tmp_AuxTable TABLE (RoleId INT)
IF SUBSTRING(@RolesID, LEN( @RolesID), 1) <> @Delimiter SET @RolesID = @RolesID + @Delimiter
SET @CurrentRole = 0
SET @Start = 0
SET @End = 0
WHILE 1=1 BEGIN
SELECT @End = CHARINDEX( @Delimiter, @RolesID, @Start )
IF @End = 0 SET @End = LEN(@RolesID)
SET @CurrentRole = CAST( SUBSTRING(@RolesID, @Start, (@End-@Start)) AS INT)
INSERT INTO @tmp_AuxTable VALUES( @CurrentRole)
IF @End = LEN(@RolesID) BREAK
SET @Start = @End+1
END
SELECT @Result = ISNULL(@Result + ' - ' , '') + RTRIM(dbo.GetRole(RoleId)) FROM @tmp_auxTable
RETURN @Result
END
GO
SELECT * FROM tmp_Roles
SELECT dbo.GetRoles( '1;2;3;', ';')
SELECT dbo.GetRole(1)
GO
INSERT INTO tmp_Roles (rolename) VALUES ('Admin')
INSERT INTO tmp_Roles (rolename) VALUES ('User')
INSERT INTO tmp_Roles (rolename) VALUES ('Role001')
INSERT INTO tmp_Roles (rolename) VALUES ('Role002')
INSERT INTO tmp_TestingTable (roles) VALUES('1;2;3')
INSERT INTO tmp_TestingTable (roles) VALUES('4')
INSERT INTO tmp_TestingTable (roles) VALUES('2;3')
INSERT INTO tmp_TestingTable (roles) VALUES('3')
INSERT INTO tmp_TestingTable (roles) VALUES('1;4;3')
SELECT id_test, dbo.GetRoles(Roles, ';') FROM tmp_TestingTable
DROP TABLE tmp_Roles
DROP TABLE tmp_TestingTable
DROP FUNCTION GetRoles
DROP FUNCTION GetRole
November 1, 2005 at 9:33 am
declare @x varchar(2000), @sql nvarchar(4000)
exec dbo.procJoinColuna 'rolename','14;0;-1;-2;',';', @x OUTPUT
set @sql = 'select ' + @x + ', y from table'
EXEC(@sql)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply