How to get out EXEC(T-SQL) data?

Question

How to get out EXEC(T-SQL) data?

Mithrandir

SSCrazy

Points: 2628
More actions
March 3, 2004 at 11:59 am

#161044
Hi, I'm creating one SP to get the result to deal with him before send a result to user, but I can't extract @RESULT from make some validations, but I get:
"Server: Msg 137, Level 15, State 1, Line 3
Must declare the variable '@RESULT'"
You can help me?
This style of query has been learn by probe-error (specially that ugly way to introduce variables to OPENQUERY), maybe you can recommend me some better way?
```
CREATE PROCEDURE dbo.spS_UserName(
@LOGIN VARCHAR(30)
)AS

DECLARE @SQL VARCHAR(300)
DECLARE @RESULT VARCHAR(100)

SET @SQL = '
SELECT @RESULT= nombre 
FROM OPENQUERY(linkedServer, ''
SELECT lastname + '''', '''' + name  AS name 
FROM dbo.tblEmployees
WHERE user_id = ''''' + @LOGIN + '''''''
) AS RemoteQuery
'
EXEC (@SQL)
GO
```
To finally add some like this (to return user):
```
IF @RESULT IS NULL
SELECT 'Unidentified User'
ELSE
SELECT @RESULT
```
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

cmore SSCrazy Points: 2452 More actions · Answer 1

The execute(string) statement cannot see any variable declarations except those declared inside the exec string itself. try using SP_ExecuteSQL. As long as you don't have enormously long string executes it works quite well, and accepts input/output parameters (although you do have to define them).

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

GO is a batch separator. Your variable will be out of scope after a GO. You might also want to consider the use of sp_executesql as this one feature an OUTPUT parameter. For more information on this, look at this

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

How about just...

CREATE PROCEDURE dbo.spS_UserName(

@LOGIN VARCHAR(30))

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(300)

SET @SQL = '

SELECT nombre

FROM OPENQUERY(linkedServer, ''

SELECT lastname + '''', '''' + name AS nombre

FROM dbo.tblEmployees

WHERE user_id = ''''' + @login + '''''''

&nbsp RemoteQuery'

EXEC (@SQL)

Note: did not test, so above is to show method only. Notice the SET NOCOUNT ON... for calls from VB etc. s that "nnn row(s)..." messages are suppressed.

On another note.... I've seen a couple or few scripts in formums the last few days that are attempting to use "Table Variables" in a manner that #TempTables work much better for, and the scripts would work if they simply used #TempTable instead of @Table. @Table scope = same as any other @Var scope, thus not directly manipulatable from with the dynamic SQL the script executes. Whereas, if script used #TempTable, the dynamic SQL of the script would be able to manipulate the table's data.

Once you understand the BITs, all the pieces come together

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Why use openquery at all? You could just do:

Select

@result = nombre

from

linkedserver.db.dbo.tblEmployees

where

user_id = @login

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Mithrandir SSCrazy Points: 2628 More actions · Answer 5

I know, but correct me if I'm wrong...

I've readed that server.db.user.object usage get ALL data to server, and the local server perform the filter/join/etc.

And using OpenQuery the filtering/join/etc is made in the remote server and I get only the results of that statement.

¿Is this true?

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Interesting point Mith...

Can you post what the estimated execution plan shows when using Jack's syntax? Does it show anything? because OPENQUERY just shows "Remote Scan" which is useless to determine overall performance implications especially when JOINing an OPENQUERY.

Can you provide feedback on execution time when using each method especially when the WHERE clause filters to a small number of records.

Thanks

Once you understand the BITs, all the pieces come together

noeld SSC Guru Points: 96590 More actions · Answer 7

FROM BOL:

------------------------------------------------------------

Remote Query Execution

SQL Server attempts to delegate as much of the evaluation of a distributed query to the SQL Command Provider as possible. An SQL query that accesses only the remote tables stored in the provider's data source is extracted from the original distributed query and executed against the provider. This reduces the number of rows returned from the provider and allows the provider to use its indexes in evaluating the query.

.....

------------------------------------------------------------------------

There are also other conditions to determine if it is going to be local or remote like column types etc.

The only thing that is granted is that with OPENQUERY you force the delegation and with four part names it may not happen

The above case will be delegated

HTH

For more see Optimizing Distributed Queries

* Noel

ThomasH SSCertifiable Points: 7437 More actions · Answer 8

Ok Noel, we get the point... what happened a "triple-click" ?

Once you understand the BITs, all the pieces come together

noeld SSC Guru Points: 96590 More actions · Answer 9

I am really Sorry, my browser got locked up and must have taken the extra posts. I think I will recomend to the forum developers to Disable the summit button after is pressed for the first time

* Noel

ThomasH SSCertifiable Points: 7437 More actions · Answer 10

I understand

I know you can "edit" your post. Noel, can you try to "delete" 2 of the 3 posts? or maybe at least edit them to loose thier contents?

Hey I just "edited" this post, and the "edit post" screen has a "delete post" button.

Once you understand the BITs, all the pieces come together

Mithrandir SSCrazy Points: 2628 More actions · Answer 11

Hmmm, I don't understand.

Execution Plan shows:

Estimated Subtree Cost = 3.3 and estimated row count = 10000 for OpenQuery (performs a Remote Scan).

Estimated Subtree Cost = 0.0388 and estimated row count = 86 for server...table (performs a Remote Query).

The tested table has about 500 records and the query performed return 1 single record.

(I don't know how to get a printable exec plan to show you :-P)

ThomasH SSCertifiable Points: 7437 More actions · Answer 12

Thanks Mith

I'm kind of interested in actual timed results with a number of records that would realy make a difference... maybe 500K rows down to a couple or few.

And... what happens when a JOIN is done with a local table. Maybe some permutations having the remote query within a derived table context by itself, and any difference if its JOIned directly ???

Once you understand the BITs, all the pieces come together

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

I know you can "edit" your post. Noel, can you try to "delete" 2 of the 3 posts? or maybe at least edit them to loose thier contents?

Not only can you delete the post, but when you delete it the points are deducted from your overall score.

That's an improvement over Snitz forum. But I agree some feature like flood control shoúld be activated.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jhouston SSC Veteran Points: 290 More actions · Answer 14

Your EXEC doesn't yet know about the parameter.

Try this.

SET @SQL = '

SELECT @RESULT= nombre

FROM OPENQUERY(linkedServer, ''

SELECT lastname + '''', '''' + name AS name

FROM dbo.tblEmployees

WHERE user_id = ''''' + @login + '''''''

) AS RemoteQuery

'

sp_ExecuteSql @SQL, '@RESULT VARCHAR(30)', @RESULT

You have to declare for the EXEC statement as well as your proc.

Hope that helps.

Tip: If you had multiple variable assigments it would be

sp_ExecuteSql @SQL, '@RESULT VARCHAR(30) OUTPUT,@VAR INT', @RESULT, @VAR

so the first argument after the SQL a comma seperated string of parameters(all params in same string) and then each argument separated by comma