Using Stored Procedures as a field (SQL Server 2008)
-
Hi, I have made a stored procedure witch returns a field that i want to use in a query. There is 1 parameter that I give to the stored procedure witch comes from the normal query. The info about the stored procedure: Name: SP_Calculate Input: ID Output: table with 1 column: Amount and 0 or 1 row. And this is what I want to do in the query:
SELECT
db1.dbo.table1.ID as ID,
db1.dbo.table1.Field1 as Field1,
db2.dbo.table2.Field2 as Field2,
( SELECT Amount FROM db1.dbo.SP_Calculate(db1.dbo.table1.ID) ) As AmountFROM db1.dbo.table1
INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.IDOf course this code doesn't work, but i hope it gives you a good view of what I try to do. This query is a example query, the actual query is really long.
-
Hi, I have made a stored procedure witch returns a field that i want to use in a query. There is 1 parameter that I give to the stored procedure witch comes from the normal query. The info about the stored procedure: Name: SP_Calculate Input: ID Output: table with 1 column: Amount and 0 or 1 row. And this is what I want to do in the query:
SELECT
db1.dbo.table1.ID as ID,
db1.dbo.table1.Field1 as Field1,
db2.dbo.table2.Field2 as Field2,
( SELECT Amount FROM db1.dbo.SP_Calculate(db1.dbo.table1.ID) ) As AmountFROM db1.dbo.table1
INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.IDOf course this code doesn't work, but i hope it gives you a good view of what I try to do. This query is a example query, the actual query is really long.
If you are expecting one (or no) value why not use a user defined function? The function could return NULL for no value
-
If you are expecting one (or no) value why not use a user defined function? The function could return NULL for no value
Could you give me an example how to use a function in my query? I have never used functions in sql server, only views/stored procedures and triggers. Converting my stored procedure to a function would not be the biggest problem, i think.
-
Hi, I have made a stored procedure witch returns a field that i want to use in a query. There is 1 parameter that I give to the stored procedure witch comes from the normal query. The info about the stored procedure: Name: SP_Calculate Input: ID Output: table with 1 column: Amount and 0 or 1 row. And this is what I want to do in the query:
SELECT
db1.dbo.table1.ID as ID,
db1.dbo.table1.Field1 as Field1,
db2.dbo.table2.Field2 as Field2,
( SELECT Amount FROM db1.dbo.SP_Calculate(db1.dbo.table1.ID) ) As AmountFROM db1.dbo.table1
INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.IDOf course this code doesn't work, but i hope it gives you a good view of what I try to do. This query is a example query, the actual query is really long.
Hi, I already got the solution, thanks to djj55: I converted the Stored Procedure into a Database Function, now i can use it in the query like this:
SELECT
db1.dbo.table1.ID as ID,
db1.dbo.table1.Field1 as Field1,
db2.dbo.table2.Field2 as Field2,
db1.dbo.Calculate(db1.dbo.table1.ID) As AmountFROM db1.dbo.table1
INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.ID -
Could you give me an example how to use a function in my query? I have never used functions in sql server, only views/stored procedures and triggers. Converting my stored procedure to a function would not be the biggest problem, i think.
A function is very similar to a stored procedure. If your stored procedure is db1.dbo.SP_Calculate(db1.dbo.table1.ID) the your function could be db1.dbo.udf_Calculate(db1.dbo.table1.ID) See BOL for a complete explanation of CREATE FUNCTION (scalar).
USE DB1
GO
CREATE FUNCTION dbo.udf_Calculate ( @inID VARCHAR(10))
RETURNS money
AS
DECLARE @myout MONEY;
SELECT @myout = amount from mytable where ID = @inID;
RETURN(@myout);This is a quick of the cuff example. Good luck
-
Hi, I already got the solution, thanks to djj55: I converted the Stored Procedure into a Database Function, now i can use it in the query like this:
SELECT
db1.dbo.table1.ID as ID,
db1.dbo.table1.Field1 as Field1,
db2.dbo.table2.Field2 as Field2,
db1.dbo.Calculate(db1.dbo.table1.ID) As AmountFROM db1.dbo.table1
INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.IDGlad you got it. It took me a while to type my last replay as I am at work. :)