Pivot table return from UDF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot table return from UDF

Author

Topic

lorijanes
Starting Member

3 Posts

Posted - 2010-09-29 : 12:39:27

I have a working Pivot Table in a stored procedure.
Now, I want to be able to join to it from other db queries
so I'm trying to put it in a UDF. But, this is my first UDF.

Any UDF experts there ? Can someone help me get this correct ?

I want to have it variable on the columns so users/customers can have different number and name of their contract_charicteristics.

Here is the working Stored Proc:

Declare @cols NVarchar(2000)
Select @cols = coalesce(@cols + ',[' + Description + ']' , '[' + Description + ']')
From CharicteristicTypes

declare @query nvarchar(4000)
set @query = N'Select ContractID, '+
@cols +'
FROM (
select CC.ContractID ,
CT.Description as ColName, Char.Description as CharDesc
from contract_charicteristics as CC
inner join charicteristics as Char
on CC.CharicteristicID = Char.CharicteristicID
inner join charicteristictypes as CT
on Char.CharicteristicTypeID
= CT.CharicteristicTypeID
)
p PIVOT ( MAX([CharDesc])
For ColName in ('+ @cols +' ) )AS pvt '

Execute(@Query)
GO

----------------
Here is my attempt as a UDF:
This returns the query string and not the results of executing the query string. I have multiple attempts to make it load the table with the executed @query but thought this would be best code to show you what I was trying to do. I couldn't get it to let me use @cols as the table definition (replace "str nvarchar(2000)" with @cols.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_Contract_Charicteristics] (@Parm int)
RETURNS @t TABLE (str nvarchar(2000))
AS
BEGIN

Declare @cols NVarchar(2000);
Select @cols = coalesce(@cols + ',[' + Description + ']' , '[' + Description + ']')
From CharicteristicTypes;

declare @query nvarchar(4000)
set @query = N'Select ContractID, '+
@cols +'
FROM (
select CC.ContractID ,
CT.Description as ColName, Char.Description as CharDesc
from contract_charicteristics as CC
inner join charicteristics as Char
on CC.CharicteristicID = Char.CharicteristicID
inner join charicteristictypes as CT
on Char.CharicteristicTypeID
= CT.CharicteristicTypeID
)
p PIVOT ( MAX([CharDesc])
For ColName in ('+ @cols +' ) )AS pvt '

INSERT @t
values (@Query)

RETURN
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 13:02:23

you cant use dynamic sql in function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lorijanes
Starting Member

3 Posts

Posted - 2010-09-30 : 14:15:54

If I have to use a hardcoded query, ... then I'll have to hardcode the column names.
That's doable, not preferrable, but doable.

I have tried that too, and tried to "EXEC sp_executeSQL @Query " to load the table with results set, but received error on the EXEC.

Below I have changed to hardcoded column names, and the attempt to EXEC into the table.

What does it take to load the query results into the table to return the table ?

ALTER FUNCTION [dbo].[fn_Contract_Charicteristics] (@Parm int)
RETURNS @t TABLE (str nvarchar(2000))
AS
BEGIN

declare @query nvarchar(4000)
set @query = N'Select ContractID,
ColumnName1, ColumnName2, ... ColumnName10
FROM (
select CC.ContractID ,
CT.Description as ColName, Char.Description as CharDesc
from contract_charicteristics as CC
inner join charicteristics as Char
on CC.CharicteristicID = Char.CharicteristicID
inner join charicteristictypes as CT
on Char.CharicteristicTypeID
= CT.CharicteristicTypeID
)
p PIVOT ( MAX([CharDesc])
For ColName in (ColumnName1, ColumnName2, ... ColumnName10) )AS pvt '

INSERT @t
EXEC sp_executesql @Query

RETURN
END
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-30 : 14:52:56

The point is that you cannot do any dynamic SQL in a function, including EXEC sp_executesql. A function will not work for what you're trying to do.

Since you have a stored procedure that does work, and it uses dynamic SQL, it's better to modify it so it can accept the tables/queries you want to join to.

lorijanes
Starting Member

3 Posts

Posted - 2010-09-30 : 17:30:40

Thanks. Yes, I have gone back to the Stored Proc and got it doing what I wanted using a derived table for the Pivot table and then joining that back to the other tables.

Thanks both of you for your help on this. It made me go look another direction and that saved me time to get it working. So, Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources