functions in select statements
-
Hi there,
recently came across and modifed a great function that allows me to return multiple results (x,y,z) in a single returned value.
So now I can do: select * from WarrantCodesByAcct() where intMasterAcctID=1 and get back a result set.
intMasterAcctID|Codes
1 | 100,200
So now I want to take it one step further... I want to pass in the intMasterAcctID and be able to use this as part of another sub query.
SELECT
MR.intMasterAcctID,
MR.vchrMap,
MR.vchrBlock,
MR.vchrLot,
(Select Codes from WarrantCodesByAcct()) WarrantCode,
(Select WarrantPerc from WarrantsCodesByAcct()) WarrantCodePerc
FROM
MasterRecord MR
I cannot use WarrantCodesByAcct(intMasterAcctID) because it thinks I'm trying to pass it a function parameter and not a value and I cannot use WarrantCodesByAcct(@intMasterAcctID) because there is no way to declare and set the value within the select.
Best I have managed to come up with with a cursor that selected from MasterRecord, defined @intMasterAcctID and then passed it in but as I am calling this as a procedure from ASP.NET it returns 250 individual result sets instead of 1 with 250 results.
Any thoughts? The function I found on here was someone elses solution to a problem of horses with multiple owners and their percentage of ownership. I was able to modify it to fit a situation I have here and I was very happy with the results... except that I cant figure out how to implement it
Regards,
Chris
-
Hi Chris
I think you're asking function arguments to be what they were not meant to, namely join fields.
Table functions with arguments come in handy where you need to restrict a maybe complex query by a single value - in this case it helps to hide the complexity of the query, yet enables all parts of the query to be parameterized.
Sometimes using views is a better choice than table functions. Where joins are needed to reduce the row number of a result set just to reach a manageable size, you shouldn't use a table function, because table functions always generate the full result set (even though it resides in a ram table). If this is your case, use a view instead.
I don't think that a cursor should be necessary in your case. You're actually emulating a join that way - it surely cannot be the most elegant way to do it.
What are you trying to do in that function? Why is it necessary to keep it in a function?
Please paste some more code...
Mads Holm
-
If you rewrite the function and let it return the Codes string with intMasterAcctID as a parameter then you can do something like this:
SELECT intMasterAcctID, dbo.WarrantCodesByAcct(intMasterAcctID)
FROM MasterRecord
Hope this helps,
Jorg
Jorg Jansen
Manager Database Development
Infostradasports.com
Nieuwegein
The Netherlands -
If I was to do a select statement from the [Warrants] table below off of intMasterAcctID I would get two rows returned for intMasterAcctID=1
The function basically returns the intMasterAcctID, Warrants and Warrant Percents
So:
Select * from WarrantCodesByAcct(2) returns
2,'PP',100
select * from WarrantCodesByAcct(1) (with 2 qualifying records)
1,'PP,FA','50,50'
Basic table layout w/data
warrants
intID,intMasterAcctID,intWarrantCodeID,intWarrantPerc1
1,1,1,50
2,2,1,100
3,3,1,100
4,1,2,50
WarrantCodes
intID,vchrCode,vchrDescription
1,'PP','Personal Property'
2,'FA','Farm Animals'
MasterRecord
intMasterAcctID,vchrOwner
1,'Chris'
2,'Bob'
3,'Fred'
Foreign key reference between Warrants.intWarrantCodeID=WarrantCodes.intID
Warrants.intMasterAcctID==MasterRecord.intMasterAcctID
CREATE FUNCTION dbo.WarrantCodesByAcct (@intMasterAcctID int)
RETURNS @resultTable
TABLE (intMasterAcctId int primary key,
Warrants char (50) null,
Warrants_Perc char(50) null)
AS
BEGIN
--declare temp tables
DECLARE @Warrants_temp
TABLE ( intMasterAcctID_ int ,
intID_ int,
Warrant char(50) null,
WP char(50) null)
DECLARE @MasterRecord_Temp
TABLE ( intMasterAcctID int primary key,
Warrants char (50) null,
_lastintID char(50) null,
Warrants_Perc char(50) null,
_lastWP char(50) null)
--copy all Warrants - relation table to temp [we can delete]
If exists(Select intMasterAcctID from Warrants where intMasterAcctID=@intMasterAcctID)
Begin
INSERT INTO @Warrants_temp
SELECT intMasterAcctID, Warrants.intID, vchrCode, intWarrantPerc1
FROM WarrantCodes join Warrants ON (WarrantCodes.intID=Warrants.intWarrantCodeID)
where intMasterAcctID=@intMasterAcctID
--copy all MasterRecords with no owner information
INSERT INTO @MasterRecord_Temp
SELECT intMasterAcctID, '','' ,'','' FROM MasterRecord where intMasterAcctID=@intMasterAcctID
--repeat if needed
WHILE (SELECT count(*) FROM @Warrants_temp)>0
BEGIN
--update Masterrecord info
UPDATE @MasterRecord_Temp
SET Warrants=rtrim(Warrants)+rtrim(Warrant)+',',
_lastintID=intID_,
Warrants_Perc=rtrim(Warrants_Perc)+rtrim(WP)+','
FROM @Warrants_temp
WHERE (intMasterAcctID=intMasterAcctID_ )
--delete already inserted Warrant info
DELETE @Warrants_temp FROM
@MasterRecord_Temp WHERE intID_=_lastintID AND intMasterAcctID=intMasterAcctID_
END --repeat if any Warrant records exists
--copy all records to result table without last comma
INSERT INTO @resultTable
SELECT intMasterAcctID, left(Warrants,len(Warrants)-1), Left(Warrants_Perc,len(Warrants_Perc)-1) FROM @MasterRecord_Temp
end
RETURN
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply