asked on

assign value of results from a function within a stored procedure to a variable

in MS SQL 2005

in stored procedure:

DECLARE @Achieved int

            SELECT @Function = ROUTINE_SCHEMA+'.' +@Function
            FROM information_Schema.Routines
            WHERE ROUTINE_NAME = @Function

            SET @FunctionText = N'SELECT @Achieved = dbo.' + @Function + N'("' + cast(@Customer_ID as varchar(5)) + N'", "' + cast(@date as varchar(12)) + N'")' ------
            print @FunctionText

             SET @Achieved = (??????)

How do I set @achieved to the value returned by the function? It's not working now

Thanks in advance!
Chris

SQL_SERVER_DBA

@Achieved is declare as INT

are you trying to assign it a string value on this peice???
@Achieved = dbo.'

JCM0165

ASKER

@Achieved is declare as INT yes

are you trying to assign it a string value on this peice???
@Achieved = dbo.'

the string is intended to query the function which produces an integer:

to simplify:

ALTER FUNCTION [fasterfitness].[F_SELECT_FITPOINT_ADHERENCE]
(
@Customer_ID int,
@date datetime
)
RETURNS TABLE AS

RETURN
(
      -- Add the SELECT statement with parameter references here
      SELECT 1 as accomp
)

Guy Hengel [angelIII / a3]

DECLARE @Achieved int

SELECT @Function = ROUTINE_SCHEMA+'.' +@Function
FROM information_Schema.Routines
WHERE ROUTINE_NAME = @Function

SET @FunctionText = N'SELECT @Achieved = dbo.' + @Function + N'("' + cast(@Customer_ID as varchar(5)) + N'", "' + cast(@date as varchar(12)) + N'")' ------
print @FunctionText
exec sp_executesql @FunctionText, N'@Achieved int OUTPUT', @Achieved output

JCM0165

ASKER

tried angellll's solution and got error

Cannot find either column "fasterfitness" or the user-defined function or aggregate "fasterfitness.F_SELECT_FITPOINT_ADHERENCE", or the name is ambiguous.

(the function name is fasterfitness.F_SELECT_FITPOINT_ADHERENCE)

Guy Hengel [angelIII / a3]

several things:
* what does print @FunctionText actually print?
* as you use dynamic sql, the permissions needed will be those of the caller of the procedure, and not the ones of the owner of the procedure.
* are you sure that you are in the correct database?

ASKER CERTIFIED SOLUTION

JCM0165

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial