set variable from openquery

Question

Post reply

set variable from openquery

timscronin

SSCertifiable

Points: 6851
More actions
July 12, 2011 at 1:09 pm

#253574

I have the following openquery I need to set a variable for to process.
THis below returns the value
select * FROM OPENQUERY(SDW, 'SELECT count(*) FROM PHARMADM.PHARM_HRXS where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')
GO
I tried
declare @cnt int
SEt @cnt = select * FROM OPENQUERY(SDW, 'SELECT count(*) FROM PHARMADM.PHARM_HRXS where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')
GO

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

bopeavy SSCertifiable Points: 5869 More actions · Answer 1

This should do what your looking for:

declare @cnt int

SELECT @cnt = COUNT(*) FROM SDW.PHARMADM.PHARM_HRXS where POSTED > to_date('07/11/2011' ,'mm/dd/yyyy'))

select @cnt

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

timscronin SSCertifiable Points: 6851 More actions · Answer 2

can't do that have to use openquery or it will ignore indexes

bopeavy SSCertifiable Points: 5869 More actions · Answer 3

Then what is the purpose for the variable? A little more info in what your trying to do would help.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

bopeavy SSCertifiable Points: 5869 More actions · Answer 4

Then try this should work for OpenQuery:

declare @cnt int

select @cnt = cnt FROM OPENQUERY(SDW, 'SELECT count(*) as cnt FROM PHARMADM.PHARM_HRXS where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')

select @cnt

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Try this form (changes to your original query bolded):

DECLARE @cnt INT

SELECT @cnt = cnt

FROM OPENQUERY(SDW, 'SELECT count(*) as cnt

FROM PHARMADM.PHARM_HRXS

where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bopeavy SSCertifiable Points: 5869 More actions · Answer 6

opc

Its deja vu.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

That's scary, we even picked the same alias and variable names :blink:

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Kelvin Lush SSCrazy Points: 2602 More actions · Answer 8

Does anyone have any idea how to do this when the remote query contains an unknown (i.e. a variable) for the "to_date" instead of "07/11/2011" (which I presume is 7th Nov 2011 !)?

You would need to execute the whole query as a string and that puts the local variable out of scope.

The only thing I can think of is to create a temporary table and populate it using sp_executesql where the string contains the OPENQUERY query.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

Kelvin Lush (3/19/2015)
Does anyone have any idea how to do this when the remote query contains an unknown (i.e. a variable) for the "to_date" instead of "07/11/2011" (which I presume is 7th Nov 2011 !)?
You would need to execute the whole query as a string and that puts the local variable out of scope.
The only thing I can think of is to create a temporary table and populate it using sp_executesql where the string contains the OPENQUERY query.

Using the previous example code as the starting point, reworking yourself into a pattern like this might help you and should keep you working in declared-SQL:

DECLARE @results TABLE (cnt INT);

DECLARE @sql NVARCHAR(MAX) =

N'SELECT count(*) as cnt

FROM PHARMADM.PHARM_HRXS

WHERE POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')';

INSERT INTO @results (cnt)

EXEC(@sql) AT [SDW];

* Note this code is untested because I do not have an environment available at the moment where I can use a Linked Server.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

-- Tested and working

DECLARE @Statement VARCHAR(1000), @Year SMALLINT, @AgencyCode VARCHAR(5)

SELECT @Year = 2014, @AgencyCode = '57LHS'

SET @Statement = '

SELECT

[Complaint Number] = QNUMBM,

[Complaint Date] = QCMPDM,

[Complaint Reason] = QCRSNM

FROM OPENQUERY(

[AS400_DB2],

''SELECT QNUMBM, QCMPDM, QCRSNM

FROM QMPLNT

WHERE QCMPDM BETWEEN ' + CAST(@Year*10000 AS CHAR(8)) + ' AND ' + CAST((@Year+1)*10000 AS CHAR(8)) + '

AND QAGYCD = ''''' + @AgencyCode + '''''

AND QCSQNM = 1''

)';

--PRINT @Statement -- visual syntax check

IF OBJECT_ID ('tempdb..#Complaints') IS NOT NULL DROP TABLE #Complaints;

CREATE TABLE #Complaints ([Complaint Number] INT, [Complaint Date] INT, [Complaint Reason] VARCHAR(6));

INSERT INTO #Complaints ([Complaint Number], [Complaint Date], [Complaint Reason])

EXEC(@Statement);

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden