How to run store procedure in where statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to run store procedure in where statement?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-20 : 17:22:05

I have a store procedure getname which return one record for last name.

How to run it in where statement like below?

select * from authors where au_lname = (exec getname)

I knew it will not work but how to run?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-20 : 17:44:08

It can't run if it doesn't work.
You can however make a loopback linked server.

SELECT * FROM Authors WHERE au_lname = (SELECT col1 FROM OPENQUERY(Myself, 'EXEC GetName')

N 56°04'39.26"
E 12°55'05.63"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 18:30:13

It may be faster to call the stored procedure and then run a select query. Linked servers can be pretty slow.

This is using an output parameter:

DECLARE @s varchar(50)

EXEC getname ..., @s=@s OUTPUT

SELECT * FROM authors WHERE au_lname = @s

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-21 : 10:12:30

I tried tkizer way but only list au_lname.

"SELECT * FROM authors WHERE au_lname = @s" list nothing.

Below is my store procedure. what is wrong?

ALTER PROCEDURE [dbo].[getname]
@s varchar(50) output
AS
BEGIN
select au_lname from authors where au_lname = 'white'
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 10:30:39

[code]ALTER PROCEDURE dbo.getname
(
@s varchar(50) output
)
AS
set nocount on

select @s = au_lname from authors where au_lname = 'white'
go

DECLARE @n VARCHAR(50)

EXEC dbo.getname @n OUTPUT

SELECT * FROM authors WHERE au_lname = @n
[/code]

N 56°04'39.26"
E 12°55'05.63"

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-21 : 10:43:19

Once change
select au_lname from authors where au_lname = 'white'
to
select @s = au_lname from authors where au_lname = 'white'
it works now.
Thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources