Replacing function call with procedure execution in sql

Question

I had a function called dbo.Match.It was an inline TVF that I replaced with a procedure called dbo.Match that has a select statement at its end to select rows from a table so that I can direct the results of select query when I execute dbo.Match to a temporary table called #Temp.

Now if it was a function, I was using this query :

if @MotherFN is not null 
  begin

    SELECT @constVal = FunctionWeight 
    FROM   dbo.FunctionWeights 
    WHERE  FunctionWeights.FunctionId = 20;
    INSERT INTO #Temp2                   
(RowNumber,ValFromUser,ColumnName,ValFromFunc,FuncWeight,percentage)
  SELECT RowId, 
         @MotherFN  ,
        'mothersfirstname'
        ,PercentMatch,
         @constVal,
         PercentMatch * @constVal
  FROM   dbo.Match(@MotherFN)

    end

Now, I need to execute dbo.Match procedure instead of dbo.Match function.How I may make this execution call and insert data in #Temp table like I was doing with function call ?

Problem : Calculating PercentMatch * @constVal and inserting in #Temp in same step efficiently. Procedure dbo.Match would return rowId and PercentMatch only. I need to insert values of RowId and PercentMatch in #Temp along with value of @constVal and a value for result of multiplication of PercentMatch and @constval

M.Ali · Accepted Answer · 2015-03-21 17:14:58Z

2

I would make the procedure accept these following parameters

@MotherFN , @constVal

And do the following inside the procedure , in select statement that returns the procedure's result set.

  SELECT RowId, 
         @MotherFN  ,               --<-- In proc definition 
        'mothersfirstname'
        ,PercentMatch,
         @constVal,                 --<-- In proc definition 
         PercentMatch * @constVal   --<-- In proc definition

And for insert simply do

INSERT INTO #TemP (RowNumber,ValFromUser,ColumnName
                       ,ValFromFunc,FuncWeight,percentage)
Exec dbo.Match(@MotherFN , @constVal)

answered Mar 21, 2015 at 17:14

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Simran Over a year ago

I thought of it too but I consider it would be passing too many arguments unnecessarily just to get these back where they were passed from. Has to be something better than that for the problem

M.Ali Over a year ago

Two arguments are not two many arguments :) , Also passing these parameters to a stored procedure and getting them back, will be better than doing them outside of the procedure, as whatever is done inside a procedure will be (execution plan) cached and will have better performance as compare to if you were to do it out side of a proc.

Simran Over a year ago

@M. Ali : Oh, is that ? Thank you :)I had no idea it may have better performance for that reason. Thanks a ton for clarifying.

Laurence · Accepted Answer · 2015-03-21 17:09:52Z

You options are more limited with a procedure.

You can use insert into ... exec ... to insert the results of the procedure into a temporary table, but you can't really combine it with another query. (Well you could use openrowset with dynamic SQL, but that will get nasty very quickly).

For example something like:

if @MotherFN is not null 
begin
    select
        @constVal = FunctionWeight 
    from
        dbo.FunctionWeights 
    where
        FunctionWeights.FunctionId = 20;

    insert into #Temp2 (
        RowId, ColumnName, ValFromFunc
    ) exec
        dbo.Match(@MotherFN);

    update
        #Temp2
    set
        ValFromUser = @MotherFN,
        FuncWeight = @constVal,
        percentage = PercentMatch * @constVal;
end;

Collectives™ on Stack Overflow

Replacing function call with procedure execution in sql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related