Re: Question on OpenQuery

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Chris (anonymous_at_discussions.microsoft.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 10:31:05 -0800

Hi Adam,
I follow your example and come up with this:

create function GetQuantityDeliveredByStore(

@storeID varchar(10),
@productNumber varchar(10),
@coverDate varchar(10)
)

returns INT AS

BEGIN

declare @returnval INT

select @returnval = sum(quantity + overage - notdeliv - short - damaged) from OPENQUERY(PROGLINK, 'select quantity,overage,notdeliv,short,damaged
from history where id =' + '"' + (@storeID) + '"' 'and number =' + (@productNumber) + 'and cover-date =' + (@coverDate) + '''')

return @returnval

end

The query is runs excellent if I assign values instrad of the variables(@storeID, @productNumber, @coverDate) like this

declare @test int

select @test = sum(quantity + overage - notdeliv - short - damaged) from OPENQUERY(PROGLINK, 'select quantity,overage,notdeliv,short,damaged
from history where id = "s0912070" and number = 92219 and cover-date = 08/25/2003')

select @test

but when I try to create the function above I get an error

Server: Msg 170, Level 15, State 1, Procedure GetQuantityDeliveredByStore, Line 19
Line 19: Incorrect syntax near '+'

Can I really pass the variables in the openquery by using the'+'?

Thanks

Relevant Pages