Best way to see if existing rows in a table using openquery

Hello,
I want to find out if there are actual rows in another database (Oracle) using openquery. (using Sql Server linked server)
This is going to be using a parameter that is used within the query itself
I know I cannot use a parameter within an openquery but is there a workaround?

query - select 1 from parts where part_nbr = @part_nbr

--if exists pseudo-code...  the remote server is an Oracle one...
if exists ( select * from openquery([prod], 'select 1 from myschema.parts where part_nbr = ' + @part_nbr + ')
     begin
            update the part
     end
else
     begin
            create the part
     end

----or can I use this?
--if exists pseudo-code...
if exists (select 1 from [prod]..[myschema].[parts] where part_nbr = @part_nbr)
     begin
            update the part
     end
else
     begin
            create the part
     end

Thanks

Using Sql Server 2012
MachinegunnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
you can use below -

best method to check for a record exists.
if exists  ( select TOP 1 1 from [prod]..[myschema].[parts] where part_nbr = @part_nbr )

Read 3. “WRITE CODE TO CHECK IF ANY RECORDS EXIST.”
from https://www.brentozar.com/archive/2015/03/five-interview-questions-to-ask-sql-server-developers/

for more detail.s

sample for you

CREATE TABLE testCounts
(
ID INT
)
GO

INSERT INTO testCounts VALUES ( 100 )
GO 10000

If EXISTS (SELECT TOP 1 1 FROM dbo.testCounts WHERE ID  = 100)
BEGIN
      PRINT 1
END

OUTPUT
1

If EXISTS (SELECT TOP 1 1 FROM dbo.testCounts WHERE ID  = 20)
BEGIN
      PRINT 1
END

OUTPUT
No rows returned.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You need to build the complete SQL including OPENQUERY as a string, then execute that with EXEC or sp_executesql.
0
Pawan KumarDatabase ExpertCommented:
for oracle - if exists ( select * from openquery([prod], 'select TOP 1 1 from myschema.parts where part_nbr = ' + @part_nbr + ')

DECLARE @part_nbr INT
      SET @part_nbr= someValue       
      EXECUTE ('SELECT TOP 1 1 vals FROM myschema.parts where part_nbr = (?);') AT [[prod]]


for other linked sql servers if exists  ( select TOP 1 1 from [prod]..[myschema].[parts] where part_nbr = @part_nbr )
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I have to object to accepting Pawan's comment #a42404818 as a solution.

The first code is wrong, you cannot use string expressions or variables in openquery. And the query itself is invalid for Oracle (because of TOP).

The second one is missing the parameter for the parametrized query, @part_nbr. It also requires that the link is RPC-enabled (openquery works without).

Only the last one might work (depends on some circumstances and settings).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MachinegunnerAuthor Commented:
Thanks for the information and clarification.
I did not use the initial openquery logic as did not work, so I tried the direct schema
approach and that worked and compiled.

Thanks.
MG

This worked:
if exists(select top 1 1 from [prod]..[myschema].[parts] where part_nbr = @part_nbr)
1
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Note that the "direct schema approach" might fail in other situations, in particular for some joins. It depends on a lot of factors. But if it works here, you should use it, as it is straightforward.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.