Stored Procedure?

[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)

 

[Sylvain Lafontaine]

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.

 

[Ryan Langton]

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!


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

 

[Ryan Langton]

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!

 

[Sylvain Lafontaine]

Make sure that you don't have any Null value creeping in.