I'm trying to set a variable to the value returned from a stored procedure. I'm using OPENQUERY to call a linked Oracle server from SQL Server 2000 but I can't seem to get the syntax right. I can't seem to get anything returned into the OUTPUT variable @weight at all... any thoughts?Here's my stored procedureset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[spGet_Mill_Weight] -- Add the parameters for the stored procedure here @wcID char(4), @StartDate char(20), @EndDate char(20), @weight int OUTPUT ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @sql_str nvarchar (4000) SET @sql_str = 'SELECT @weight = SUM(produced_wgt) AS weight FROM MIPS.coil_dtl WHERE wc_num = ''' + @wcID + ''' AND coil_num = coil_num_at_wc AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') > ''' + @StartDate + ''' AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') < ''' + @EndDate + '''' SET @sql_str = N'select weight from OPENQUERY(MIPS, ''' + REPLACE(@sql_str, '''', '''''') + ''')' --PRINT @sql_str EXEC (@sql_str) print @weightEND
and here's where I call itdeclare @weight intexec spGet_Mill_Weight '52', '2005-12-21 07:00:00', '2005-12-21 19:00:00', @weight OUTPUT IF @weight > 200begin print 'test'end