CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Simran
Simran

Reputation: 569

Replacing function call with procedure execution in sql

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

Upvotes: 1

Views: 44

Answers (2)

M.Ali
M.Ali

Reputation: 69554

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)

Upvotes: 2

Laurence
Laurence

Reputation: 10976

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;

Upvotes: 1

Related Questions