Creating Function that returns table with dynamic columns

Question

Creating Function that returns table with dynamic columns

chof

SSC Veteran

Points: 212
More actions
April 26, 2017 at 9:54 am

#342616

Hello,
This is the first time I am attempting to create a function. I want to call it from my VB.Net application to load a datagrid. The columns are dynamic, so I cannot name the columns because I don't know how many will be returned. My query works without the "Create Function". I also looking into creating a view, but you cannot use "DECLARE" in a view.
Any help is appreciated... thank you!
Here is my statement::
CREATE FUNCTION dbo.PartnerTaxAllocation (@prop AS NVARCHAR(8), @taxproj AS NVARCHAR(8)) RETURNS @T TABLE DECLARE @cols AS NVARCHAR(MAX), @query as NVARCHAR(MAX)
Select @cols = STUFF((SELECT ',' + QUOTENAME(name)        from partner p, Tax_Owner_Allocations t                     where p.ocode = t.OCode                     and t.Entity_Num = '' + @prop + ''                     and t.Projection_Year = '' + @taxproj + ''        group by p.name,p.OCode                     order by p.OCode     FOR XML PATH(''), TYPE     ).value('.', 'NVARCHAR(MAX)')    ,1,1,'')
SET @query = 'SELECT allocname, ' + @cols + ' FROM (     SELECT     ta.Allocation_Name as [AllocName],     t.Percent_Type as [PercentCode],     a.percent_name as [PercentName],     ta.Allocation_Code as [AllocCode],     p.name as [PartnerName] from partner p, Ownership o LEFT OUTER JOIN Tax_Owner_Allocations t on o.ocode = t.ocode LEFT OUTER JOIN Tax_Allocation ta on t.Allocation_Code = ta.Allocation_Code LEFT OUTER JOIN Tax_Allocation_Percent a on t.Percent_Type = a.percent_ID and o.Entity_Num = ''' + @prop + ''' and o.Tax_Projection = ''' + @taxproj + ''' where p.ocode = o.ocode group by ta.Allocation_Name, ta.allocation_code, p.name,percent_type, percent_name ) as s
PIVOT
(     SUM(Percentcode)     FOR [PartnerName] in (' + @cols + ')
    ) as pvt '         END
RETURN execute(@query + ' order by pvt.alloccode');

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

x SSC-Insane Points: 23660 More actions · Answer 1

That situation looks to have contradicting requirements, functions need defined return types but you stated you cannot define the return type ahead of time. Why not try just using the query or maybe a stored procedure instead?

Lowell SSC Guru Points: 323546 More actions · Answer 2

functions cannot use dynamic queries; you'll have to use a procedure instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chof SSC Veteran Points: 212 More actions · Answer 3

Once the stored procedure is created, how do you call it?

USE [hp_accting] GO /****** Object: StoredProcedure [dbo].[PROCPartnerTaxAllocation] Script Date: 4/26/2017 1:14:31 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROC [dbo].[PROCPartnerTaxAllocation] (@prop AS NVARCHAR(8), @taxproj AS NVARCHAR(8)) with exec as caller as

DECLARE @cols AS NVARCHAR(MAX), @query as NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME(name) from partner p, Tax_Owner_Allocations t where p.ocode = t.OCode and t.Entity_Num = '' + @prop + '' and t.Projection_Year = '' + @taxproj + '' group by p.name,p.OCode order by p.OCode FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @query = 'SELECT allocname, ' + @cols + ' FROM ( SELECT ta.Allocation_Name as [AllocName], t.Percent_Type as [PercentCode], a.percent_name as [PercentName], ta.Allocation_Code as [AllocCode], p.name as [PartnerName] from partner p, Ownership o LEFT OUTER JOIN Tax_Owner_Allocations t on o.ocode = t.ocode LEFT OUTER JOIN Tax_Allocation ta on t.Allocation_Code = ta.Allocation_Code LEFT OUTER JOIN Tax_Allocation_Percent a on t.Percent_Type = a.percent_ID and o.Entity_Num = ''' + @prop + ''' and o.Tax_Projection = ''' + @taxproj + ''' where p.ocode = o.ocode group by ta.Allocation_Name, ta.allocation_code, p.name,percent_type, percent_name ) as s

PIVOT

( SUM(Percentcode) FOR [PartnerName] in (' + @cols + ')

) as pvt '

exec(@query + ' order by pvt.alloccode');

Shows it is returning an integer... how do I get it to return a table?

x SSC-Insane Points: 23660 More actions · Answer 4

should be a result set. work on calling it from your vb.net and examine the result set. It should work just the same as if you had just sent query text from vb.net. try it with a datareader maybe?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

chof - Wednesday, April 26, 2017 12:45 PM
Shows it is returning an integer... how do I get it to return a table?

Stored procs always return an integer which is a return status. Don't worry about that, it will still return a resultset. You can call it using a SqlCommand object with a CommandType of StoredProcedure, and read it using a DataReader as Patrick mentioned, into a BindingSource which you can bind the grid to, so maybe something similar to this?


Dim dr As SqlDataReader
Dim src As New BindingSource

Using conn As New SqlConnection(Connection_String)
    Using cmd As New SqlCommand("dbo.PROCPartnerTaxAllocation", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@prop", PropValue)
        cmd.Parameters.AddWithValue("@taxproj", TaxprojValue)
        conn.Open()
        dr = cmd.ExecuteReader()
        src.DataSource = dr
        dr.Close()
        conn.Close()
        mydatagrid.DataSource = src
     End Using
End Using

chof SSC Veteran Points: 212 More actions · Answer 6

Thanks Chris, that did the trick!!

Thank you so much everyone!