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 2000 Forums
 SQL Server Development (2000)
 Help with OUTPUT parameter on OPENQUERY

Author  Topic 

Son Volt
Starting Member

4 Posts

Posted - 2005-12-21 : 10:44:18
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 procedure


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- 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

AS
BEGIN
-- 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 @weight
END



and here's where I call it




declare @weight int
exec spGet_Mill_Weight '52', '2005-12-21 07:00:00', '2005-12-21 19:00:00', @weight OUTPUT
IF @weight > 200
begin
print 'test'
end

TimS
Posting Yak Master

198 Posts

Posted - 2005-12-21 : 19:42:58
Try

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @sql_str nvarchar (4000)

create table #temp (weight int)

SET @sql_str =
'SELECT 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'INSERT INTO #temp (weight) select weight from OPENQUERY(MIPS, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

PRINT @sql_str

EXEC (@sql_str)
SET @weight = (SELECT weight FROM #temp)

print @weight

DROP table #temp
END


Tim S
Go to Top of Page

Son Volt
Starting Member

4 Posts

Posted - 2005-12-22 : 13:28:27
It worked... thanks!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-23 : 00:56:18
>>SET @weight = (SELECT weight FROM #temp)

You will get error if the query returns more than one value
It is always safer to use

Select @weight = weight FROM #temp


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

TimS
Posting Yak Master

198 Posts

Posted - 2005-12-23 : 10:56:00
SET @weight = (SELECT weight FROM #temp)
is better if you want it to error out because the logic is that it should be only one record.

Tim S
Go to Top of Page
   

- Advertisement -