March 14, 2011 at 6:35 am
Hi All,
I desperately need help.
I have created a stored proc that retrieves my data correctly from a table. I need to be able to access this data from Analysis services. But analysis services doesn't seem to like SP's, so they suggest a Table function.
I have the following Function which will not create because of the EXEC call within it.
CREATE FUNCTION fn_SBK_DIMProjects
RETURNS @retProjects TABLE
(PrjCode nvarchar(20),
PrjName nvarchar(255),
UDF1 nvarchar(255),
UDF2 nvarchar(255),
UDF3 nvarchar(255),
UDF4 nvarchar(255),
UDF5 nvarchar(255)
)
AS
BEGIN
Declare @SQLQry varchar(255)
Declare @column1 varchar(255)
Declare @column2 varchar(255)
Declare @column3 varchar(255)
Declare @column4 varchar(255)
Declare @column5 varchar(255)
--Selects the actual field name from a mapping table
Select @column1 = (SELECT U_SAPUDF
FROM [@CUBESMAP]
WHERE (U_Table = 'OPRJ') AND (U_CUBEUDF = 'UDF1'))
--Selects the actual field name from a mapping table
Select @column2 = (SELECT U_SAPUDF
FROM [@CUBESMAP]
WHERE (U_Table = 'OPRJ') AND (U_CUBEUDF = 'UDF2'))
--Selects the actual field name from a mapping table
Select @column3 = (SELECT U_SAPUDF
FROM [@CUBESMAP]
WHERE (U_Table = 'OPRJ') AND (U_CUBEUDF = 'UDF3'))
--Selects the actual field name from a mapping table
Select @column4 = (SELECT U_SAPUDF
FROM [@CUBESMAP]
WHERE (U_Table = 'OPRJ') AND (U_CUBEUDF = 'UDF4'))
--Selects the actual field name from a mapping table
Select @column5 = (SELECT U_SAPUDF
FROM [@CUBESMAP]
WHERE (U_Table = 'OPRJ') AND (U_CUBEUDF = 'UDF5'))
--Builds the query into a variable
Select @SQLQry = 'SELECT PrjCode, PrjName, ' + @Column1 + ' AS UDF1, ' + @Column2 +' AS UDF2, ' +
@Column3 + ' AS UDF3, ' + @Column4 +' AS UDF4, ' + @Column5 + ' AS UDF5
FROM OPRJ WITH (nolock)
UNION ALL
SELECT ''<None>'' AS Expr1, ''<None>'' AS Expr2, '''' AS Expr3, '''' AS Expr4,'''' AS Expr5, '''' AS Expr6, '''' AS Expr7'
--I am stumped here!!
INSERT @retProjects
exec (@SQLQry)
END
RETURN
GO
In one instance of the DB UDF1 could be U_Name, and in another instance UDF1 could be U_Version, so I need to be able to change the field name
How can I overcome this problem?
Thanx in advance
Justin Simpson
March 14, 2011 at 9:51 am
TheGrem (3/14/2011)
Hi All,I desperately need help.
I have created a stored proc that retrieves my data correctly from a table. I need to be able to access this data from Analysis services. But analysis services doesn't seem to like SP's, so they suggest a Table function.
.
.
.
I am no expert but am surprised at the fact that AS will not use stored procedures given how much press they receive as the preferred data acces method for SQL Server. Have you seen this article: http://msdn.microsoft.com/en-us/library/ms175314(v=sql.90).aspx
Please post your findings when you arrive at a solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2011 at 11:21 am
Thanx Old hand for the reply.
It could be my lack of experience in AS that is letting me and AS down here.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply