Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-10-08
6
Medium Priority
?
2,251 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:JCM0165
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20037757
@Achieved is declare as INT

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

Author Comment

by:JCM0165
ID: 20037793
@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
)

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20038593
               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


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:JCM0165
ID: 20039874
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)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20039962
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?
0
 

Accepted Solution

by:
JCM0165 earned 0 total points
ID: 20040009
fixed it myself with info from


http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply

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

            SET @FunctionText = N'SET @Achieved = (SELECT Accomp FROM ' + @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
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question