cdemott33
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](@Use rID 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?
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](@Use
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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
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
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.... =)
Thanks for sharing info.... =)
ASKER
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
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
ASKER
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?