StoredProcedures or Userdefined function.
-
Hi All,
I'm working on a reporting tool called VISION, which can extract data from underlying SQL Database and displays in Excel, Word, etc.
It lets us to create sql scripts for fetching data from the underlying database. I'm writing different queries for fetching the data from different tables according to the requirement.
Right now, i need to include few columns (derived or user defined) in an SQL Statement along with normal columns of a table. For these fields, we can write individual SQL fragments and fetch the required data. Now, i need to include a field to hold the output of a storedProcedure or a user defined funtion along with other normal columns in SELECT statement so that when we generate the report, it should appear as an other column along with other normal columns.
Can we include the output of a storedprocedure or a UDF in Select statement, if so can any one help me with the syntax. If not, hw can we go for doing the same.
One more point to mention here is, we are going to write these sql scripts using tags {} during design time and they get converted or substituted to relevant values in SELECT statement during runtime.
This sample script might also help you :
SELECT
J1.ContractNumber,
(CASE
WHEN J1.ContractNumber LIKE '1%' THEN 'BUY'
WHEN J1.ContractNumber LIKE '2%' THEN 'SELL'
WHEN J1.ContractNumber LIKE '3%' THEN 'STOCK'
WHEN J1.ContractNumber LIKE '4%' THEN 'MI'
WHEN J1.ContractNumber LIKE '5%' THEN 'DECL'
WHEN J1.ContractNumber LIKE '6%' THEN 'VESS'
END
) ContractType,
(SELECT FUNLCURR FROM TWO.dbo.MC40000) FUNLCURR
FROM TWO.dbo.TCA20008 J1
WHERE 1=1 ORDER BY 1,2,3
Now, to the same above script i need to include an other column which can hold the output of a storedproc or UDF.
Thanks in Advance,
I hope, the question is clear..
if not please let me know so i can try to write in more detail.Subhash
-
Have you tried the following:
SELECT dbo.udf_FunctionNameHere ?? Also some require () at the end..
Good Hunting!AJ Ahrens
webmaster@kritter.net -
It has to be a Function - that's it, nothing else, game over! You cannot include data from a Stored Proc into a Select Statement. The only way (I know of) to use Stored Procs in a select, is to actually execute a SP that returns a Data Set.
Example:
Select, Field1, Field2, dbo.udf_ParseData(Field3) As Field3
-
Here is a sample syntax of a function I have that looks for identical matches on the org and addresss fields.
Downsides of these functions are that you have to use the dbo. prefix to address the function, even though it is good practice, and for large sets of records to return, it can get slow.
CREATE FUNCTION dbo.Match(@Org nvarchar(3000),@Address nvarchar(3000))
--takes an org and address and returns comma list of matching IDs
--USE Select Org_ID, Org, Address, dbo.Match(Org,Address) AS Matching_IDs FROM Organization WHERE Org_ID = 5
RETURNS NVARCHAR(3000)
AS
BEGIN
DECLARE @Org_IDs NVARCHAR(3000)
SELECT @Org_IDs = ISNULL(@Org_IDs + ', ', '') + Convert(nvarchar(20),O.Org_ID)
FROM Organization O
WHERE 0.Org = @Org AND 0.Address = @Address
RETURN(@Org_IDs)
END
-
Hi All,
I tried using UDF using dbo as prefix and it worked.
Thanks for all your clarifications.
Subhash
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply