asked on
DECLARE @aID varchar(40)
DECLARE @pID varchar(40)
DECLARE @aNAME nvarchar(max)
DECLARE @aDESCRIPTION nvarchar(max)
DECLARE @aFILENAME nvarchar(max)
DECLARE @aSEARCHTEXT nvarchar(max)
DECLARE @aFIRSTNAME nvarchar(max)
DECLARE @aLASTNAME nvarchar(max)
SELECT
@aID = LTRIM(RTRIM(ASSET_ID)), @aNAME = LTRIM(RTRIM(a.Name)), @aDESCRIPTION = LTRIM(RTRIM(a.Description)), @aFILENAME = a.FileName, @aSEARCHTEXT = LTRIM(RTRIM(a.Search_Text)), @aFIRSTNAME = LTRIM(RTRIM(usert.FirstName)), @aLASTNAME = LTRIM(RTRIM(usert.LastName))
FROM IPM_ASSET a
INNER JOIN IPM_USER usert ON a.UserID = usert.UserID
WHERE
ASSET_ID = 806046
DECLARE @TAGLIST nvarchar(max)
SELECT
TOP 60
@TAGLIST = COALESCE(@TAGLIST +' ','') + LTRIM(RTRIM(t2.TAGNAME))
FROM IPM_ASSET_TAG t1
INNER JOIN IPM_TAG t2 ON t1.TAGID = t2.TAGID
WHERE
ASSET_ID = 806046
AND TAGUSE = 1
AND LEN(t2.TAGNAME) > 2
AND LEN(t2.TAGNAME) < 100
DECLARE @SERVICESLIST nvarchar(max)
SELECT
TOP 50
@SERVICESLIST = COALESCE(@SERVICESLIST +' ','') + LTRIM(RTRIM(z2.KeyName))
FROM IPM_ASSET_SERVICES z1
INNER JOIN IPM_SERVICES z2 ON z1.KeyID = z2.KeyID
WHERE
ASSET_ID = 806046
AND KEYUSE = 1
DECLARE @ILLUSTLIST nvarchar(max)
SELECT
TOP 50
@ILLUSTLIST = COALESCE(@ILLUSTLIST +' ','') + LTRIM(RTRIM(z2.KeyName))
FROM IPM_ASSET_ILLUSTTYPE z1
INNER JOIN IPM_ILLUSTTYPE z2 ON z1.KeyID = z2.KeyID
WHERE
ASSET_ID = 806046
AND KEYUSE = 1
DECLARE @MEDIALIST nvarchar(max)
SELECT
TOP 50
@MEDIALIST = COALESCE(@MEDIALIST +' ','') + LTRIM(RTRIM(z2.KeyName))
FROM IPM_ASSET_MEDIATYPE z1
INNER JOIN IPM_MEDIATYPE z2 ON z1.KeyID = z2.KeyID
WHERE
ASSET_ID = 806046
AND KEYUSE = 1
SELECT 806046 as ASSET_ID, CONCAT(@aID, ' ', @pID, ' ', @aNAME, ' ' , @aFILENAME, ' ', @aDESCRIPTION, ' ', @aSEARCHTEXT , ' ', @TAGLIST, ' ', @SERVICESLIST, ' ', @ILLUSTLIST, ' ', @MEDIALIST, ' ', @aFIRSTNAME, ' ', @aLASTNAME)