asked on

populating a tvp from a UDF table

I am trying to create a stored procedure to compare two return investment plans:
I have a UDF that needs to populate the TVP
This select statement will give me a view of the table data i wish to populate the TVP with

SELECT CustID, CustName, NumberofYears,dbo.IntRate(NumberofYears) AS rate, dbo.FinalAmount(PrincipleAmount, NumberOfYears, DepositType) AS FinalAmount
FROM Customers

I could do this with manual insert values but really would like to use a insert from query method if possible

Guy Hengel [angelIII / a3]

you can INSERT or UPDATE from a SELECT/JOIN, but it's not clear at all which data has to go where from your explanations so far ...

jrb47

ASKER

I have created a TVP

CREATE TYPE [dbo].[bestScenario] AS TABLE(
[CustID] [char](4) NULL,
[CustName] [char](3) NULL,
[Principal] [float] NULL,
[NumberOfYears] [int] NULL,
[DepositType] [char](6) NULL
)
GO

I need to populate it based on this query which pulls data from 2 UDF's
I need to do this via a stored procedure

query is:
SELECT CustID, CustName, NumberofYears,dbo.IntRate(NumberofYears) AS rate, dbo.FinalAmount(PrincipleAmount, NumberOfYears, DepositType) AS FinalAmount
FROM Customers

I found this link but it is rather confusing for me to try and relate this to what i am doing.....
http://www.dotnetspider.com/resources/35675-About-Table-Type-Table-Valued-Parameters.aspx

Guy Hengel [angelIII / a3]

the basic SQL would be like this:

INSERT INTO TVP ( customerid, custname, ... )
SELECT CustID, CustName,  ...
FROM Customers

Open in new window

jrb47

ASKER

i tried that it does not work

Guy Hengel [angelIII / a3]

>it does not work
not very clear as error description ...

jrb47

ASKER

This query does not populate the table so that the data may be used in the stored procedure that is called during the transaction.

ASKER CERTIFIED SOLUTION

dwkor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jrb47

ASKER

thank you - i knew there had to be a way!