Stored Procedure?

  • Thread starter Thread starter Ryan Langton
  • Start date Start date
R

Ryan Langton

I have a table that contains multiple numeric rows. I need a VIEW that
calculates another row displaying the percentages of each numeric row. For
example, my table has 4 columns, the first record may look like this:
FieldA = 10, FieldB = 40, FieldC = 50.
I need to generate a string field that shows the breakdown of those fields
and percentage of the total, for example: FieldD would be = "FieldA 10%
FieldB 40% FieldC 50%".
I hope I've explained that well enough, it is a unique situation. What is
the best way of doing this? (I can nearly do it just in a regular query but
I cannot put all fields into the string)
 
Why you cannot put all fields into a string with your regular query?

In your case, you can also use an UDF (User Defined Function) to make things
simpler to read when calculating the final string.
 
Sylvain,

I'm trying to use a UDF but it is only returning the last successful test.
For example, if @intGeneralFund is 20,000 and the other 2 variables are
blank, the string returned is "General Fund 20,000", which is correct.
However, if @intGeneralFund is 20,000 and @intDebt is 10,000, the string
returned is "Debt Financing 10,000" (the general fund portion of the string
disappears). Any idea why this would happen?

Also, I cannot do this in a single query because there is so much CAST'ing
and totaling and concatenating of strings, my mind gets boggled! :P


ALTER FUNCTION dbo.pGenerateFundSource (

@intGeneralFund INT = 0,

@intSpecial INT = 0,

@intDebt INT = 0)

RETURNS NVARCHAR(1024)

AS

BEGIN

DECLARE @strTemp NVARCHAR(1024)

DECLARE @strOUT NVARCHAR(1024)

SET @strOUT = N''

IF @intGeneralFund <> 0

BEGIN

SET @strTemp = @strOUT

SET @strOUT = RTRIM(@strTemp) + N'General Fund ' + CAST(@intGeneralFund AS
NVARCHAR(50)) + N' '

END

IF @intSpecial <> 0

BEGIN

SET @strTemp = @strOUT

SET @strOUT = RTRIM(@strTemp) + N'Special Funds ' + CAST(@intSpecial AS
NVARCHAR(50)) + N' '

END

IF @intDebt <> 0

BEGIN

SET @strTemp = @strOUT

SET @strOUT = RTRIM(@strTemp) + N'Debt Financing ' + CAST(@intDebt AS
NVARCHAR(50)) + N' '

END

RETURN @strOUT

END
 
Nevermind, the UDF is working. There just weren't any cases in the database
where there would be more than one value > 0 of those 3 variables!
 
Back
Top