Link to home
Create AccountLog in
Avatar of cdemott33
cdemott33Flag for United States of America

asked on

How do I call a Function from a Stored Procedure?

This is my very first attempted at creating a function so I could really use some help.

I created a new Multi-statement Table-valued function that returns a Table.  I named this function dbo.GetSomeRecords.  It looks like this....

FUNCTION [dbo].[GetSomeRecord](@UserID int)
RETURNS @MyTable TABLE
(
      CustomerID      INT         NOT NULL,
      OrderDate       DATETIME    NOT NULL
)
AS
BEGIN
Etc, etc...

Now for my Stored Procedure.  What syntax do I need to enter into my stored procedure in order to return my table?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of cdemott33

ASKER

For some reason when I start to type the dbe.getsomerecords I get a red underline stating it's an invalid object.  I tried refreshing the Functions folder but I still have the same issue?  Finally I closed SSMS 2008 and reopenned it.  When I went to my SP after I restated SSMS it FOUND the function?  

Any idea what's going on here?  Am I going to have to close and open SSMS in order for it to see the new functions?
You solutions worked!  Thank You.

As far as my question concerning the invalid object, I resoved it by refreshing the local cache for Intellisense.  

If anyone runs into this issue (Using SSMS 2008), just do the following:

- Open up a new query or your stored procedure
- Go to 'Edit', then down to 'Intellisense'
- Select 'Refresh Local Cache'

See article here:
http://www.mssqltips.com/tip.asp?tip=2291
Hi,
When you create a function/sp then object explorer is not refresh automatically so you have to just press refresh list from context menu. or parent note in oject explorer.

Thanks
Hi Author,

Thanks for sharing info.... =)
Hi IJZ - Actually that was the first thing I tried.  I went to my root node within the object explorer... right-click and chose "Refresh".  I then went into my database, open my stored procedure and tried to call the function but intellisense didn't see it.  

Who knows.  Could be a bug in my install, but at least going to Edit, Intellisense, Refresh Local Cache worked! :o)  

Thanks again for your help