October 27, 2013 at 8:15 pm
I have a store procedure that assign alphabet to columns selected by users and plotting a grid. Also have a table with weights that get assigned to each column the user select. Now I want to Rank the columns so I have created a function from the weighted table. My problem now is how to apply the weight function to the working store Procedure. See the attached file for the queries. Any help will be apprciated
October 28, 2013 at 8:05 am
Not much in the way of details to work with here. You say you want to use this function in your proc but no explanation of where or why. Your function can be rewritten as an inline table valued function instead of a scalar function. This will greatly help performance.
ALTER FUNCTION [dbo].[fnWeight]
(
@order AS int,
@Description AS int
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT cast(TitleWeight as numeric(5, 1)) as TitleWeight
FROM
db_owner.RankTable
WHERE
[order] = @order
AND [Description] = @Description
I suspect you could also greatly improve performance in your main proc by removing that while loop. Without anything to work with it is awfully difficult to tell what you are trying to do.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2013 at 9:16 am
Hi
Thanks very much. I have managed to get it working the way I want
October 28, 2013 at 9:24 am
kdaniapam (10/28/2013)
HiThanks very much. I have managed to get it working the way I want
Do you still have a scalar function? Do you still have a while loop? If either of those answers is YES I would suggest you can gain a lot of performance improvement if you want some help.
Regardless I am glad you were able to get it working.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2013 at 10:15 am
kdaniapam (10/28/2013)
HiThanks very much. I have managed to get it working the way I want
Please share the solution with us.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply