Get value from Openquery statement

You should be able to do this without dynamic SQL:

Select @Req = AUTH
From OPENQUERY(SIDPERS, 'Select SUM(AUTH) AUTH from MNLOCAL_ACN_AUTH where UPC =  ''' + @Unit + '''');

If you do use dynamic SQL, then you should use sp_executesql, passing in @Unit as an input variable and @Req as an output one. http://msdn.microsoft.com/en-us/library/ms188001.aspx

Unfortunately you cannot use variables with OPENQUERY(), which may explain why the author is resorting to using Dynamic SQL.

All,
The above statement does not work as OPENQUERY does not accept variables.  In the end I used the following work around to get it to work but there must be a better way as I have to write 15 of these with the different counts.

Create table #tmpR(VAL float)
            SET @TSQL = 'Select * From OPENQUERY(SIDPERS, ''Select SUM(AUTH) AUTH from MNLOCAL_ACN_AUTH where UPC =  ''''' + @Unit + ''''''')'
            truncate table #tmpR
            Insert into #tmpR
            EXEC (@TSQL)
            Select @Req = VAL from #tmpR
            drop table #tmpR

You could also try not utilizing OPEN Query and just referencing the FQ name of the table on the linked server:

IE:

SELECT @Req = SUM(AUTH)
FROM SIDPERS.<DBNAME>.<SCHEMA>.MNLOCAL_ACN_AUTH
WHERE UPC = @Unit

SELECT @Req

Thanks, Anthony! I never tried to use OPENQUERY with a variable before, so I was not aware of that restriction. Anyway, why did you not like the sp_executesql part of my suggestion, which would let you get the output variable w/o a temp table?

Additionally, how much overhead was there in doing a GROUP BY UPC in the open query, returning UPC as a column, and then filtering UPC in SQL?

DrewKjell:

If I run witht the FQ server names it takes just one statement 12 seconds to run, openquery does it in a second, so add oon antoher 15 of these statements * running it in a loop for 150 different @Unit that need to be counted and it runs for quite a long time versus openquery statements.  If I hard code the variable it takes 8 seconds to run the whole thing in openquery.

mwvisa1:
I have approx 150 UPC that I cycle through to get the counts with 15 different statements that count and get percentages, Grouping it was a harder solution than trying to do what I am doing.

No problem. I was just curious.

Thanks for suggestions