• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6275
  • Last Modified:

EXEC Procedure as sub query

I'm trying to do this but its not working:

SELECT * FROM Products
WHERE sDescription = (EXEC sp_GetTheinfoIneed @id)

How can I achive this? I tried setting the result to a variable and use that but that dident work either.

Any ideas?
0
LegalZoomer
Asked:
LegalZoomer
  • 3
  • 2
  • 2
  • +2
1 Solution
 
derekkrommCommented:
try having 2 parameters to your query, an input and an output:

create procedure sp_gettheinfoineed (@id int, @output varchar(8000) output) as
.....


then

declare @desc varchar(8000)
exec sp_gettheinfoineed @id, @desc

select * from products where sdescription=@desc
0
 
appariCommented:

or change your procedure to a function, then you can use it in your sql statement directly.
0
 
kenhaleyCommented:
Another way, that doesn't involve modifying the stored proc or creating a function:
Create a temp table with columns defined to receive the output of the stored proc, then you can do this:

INSERT #TEMP
   EXEC theStoredProc @id

and then ...
SELECT * FROM #TEMP WHERE...etc.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kenhaleyCommented:
But actually, if it were me, I would change the proc to a function, as appari suggested.  That's the most flexible, and most efficient.
0
 
derekkrommCommented:
FYI, the syntax for a function is:

create function GetTheInfo(@id int) returns varchar(8000) as
begin
declare @x varchar(8000)
select @x = whatever from table

return @x
end

then use it inline in your select as follows:

SELECT * FROM Products
WHERE sDescription = GetTheInfo(@id)
0
 
dbbishopCommented:
Really be easier to design if we saw the code in the procedure and had an idea of what you were trying to return (scalar, table,?)
0
 
LegalZoomerAuthor Commented:
This is my situation:

I have a Stored Proc on server "A" that will accept an order# and will return product Desc.

On Server "B" I will have another Stored Proc with also a table that has some ID info. I look up in that table by the result of the Stored proc from server "A"

Server "A" Stored Proc sp_GetDescription:
Select sDescription from .........bunch of joins where orderid = @iorder

Server "B" Stored Proc sp_GetID:
Select ID from .........bunch of joins where value = (Exec sp_GetDescription @iorder)

I am trying to get the most efficent way to get the data in terms of speed. The thing that makes most sence is to have the table from server B in server A and they all could be in one Sp but I cant do that.

Thanks



0
 
appariCommented:

you can create a linked server in serverb(linking serverA to serverB) and use the tables from servera and servern
in the same query.
0
 
derekkrommCommented:
In that case, create a linked server to server "A" from server "B". To do this, see BOL for sp_addlinkedserver if you need to setup a login for it. If you can use windows authentication, sp_addlinkedserver 'serverA' should do the trick.

Now, instead of a stored procedure, use a function like this:

create function GetTheInfo(@id int) returns varchar(8000) as
begin
declare @x varchar(8000)
select @x = whatever from servera.databasename..table where x=y

return @x
end

And to reference that function, do this in your query:

SELECT * FROM Products
WHERE sDescription = GetTheInfo(@id)

I think that covers everything!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now