Return Value from OpenQuery SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return Value from OpenQuery SP

Author

Topic

GeeCeeBee
Starting Member

2 Posts

Posted - 2008-11-10 : 00:35:16

Hello,

I've created a stored procedure that executes an OpenQuery statement. I need to pass parameters to the OpenQuery, so I've assembled the statement within a string variable that I then use EXEC upon.

I'd like to return the results of the SP so I can set a variable for further use in my app, but I can't seem to get it right...

Here's the SP:

USE [Runtime]
GO
DROP PROCEDURE [dbo].[proc_GetFlowTotal]
GO

/****** Object: StoredProcedure [dbo].[proc_GetFlowTotal] Script Date: 11/03/2008 16:06:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[proc_GetFlowTotal]
@TagName VARCHAR(255)
, @StartDT DateTime
, @EndDT DateTime
, @RetVal Float = NULL OUTPUT
AS

DECLARE @QueryStr VARCHAR(255)
DECLARE @SelectStr VARCHAR(MAX)
DECLARE @StartDTStr VARCHAR(19), @EndDTStr VARCHAR(19)
SET @StartDTStr = CONVERT(VARCHAR(19), @StartDT, 20)
SET @EndDTStr = CONVERT(VARCHAR(19), @EndDT, 20)

SET @QueryStr = "SELECT DateTime, " + @TagName + "
FROM Runtime.dbo.AnalogWideHistory
WHERE wwVersion = 'Latest'
AND wwRetrievalMode = 'Counter'
AND wwRowCount = 1
AND DateTime >= '" + @StartDTStr + "'
AND DateTime <= '" + @EndDTStr + "'"

SET @SelectStr = "USE Runtime
SET QUOTED_IDENTIFIER OFF

DECLARE @RetVal Float
SELECT " + @TagName + "
FROM OpenQuery(INSQL, " + CHAR(34) + @QueryStr + CHAR(34) + ")
--RETURN @RetVal"

EXEC (@SelectStr)

and here's my code to call the SP:

USE Runtime

DECLARE @TagName VARCHAR(255)
DECLARE @StartDT DateTime, @EndDT DateTime
DECLARE @Value FLOAT
DECLARE @Pos INT

SET @TagName = 'CougarMtn2_pumpflowtotal'
SET @StartDT = '9-1-08 8:00'
SET @EndDT = '9-1-08 9:00'

EXEC proc_GetFlowTotal @TagName , @StartDT , @EndDT

The current code above returns the result I want as if I'd used a SELECT statement, but, again, I need to capture that value and set it to another variable for further use. I've tried multiple variances of the code, both in the SP and in the calling code, but with no luck.

Any help would be GREATLY appreciated!

Thanks in advance,

gcb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 00:39:03

[code]EXEC proc_GetFlowTotal @TagName , @StartDT , @EndDT,@Value OUT[/code]

then use @Value at places where you need the value

GeeCeeBee
Starting Member

2 Posts

Posted - 2008-11-10 : 11:44:39

Hi visakh16,

Thanks for the quick reply. I tried your suggestion and then added

SELECT @Value AS 'Value'

The initial return shows the correct value, but the SELECT statement returns NULL.

Any ideas?

Thanks!
g

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources