URGENT Question about EXEC - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

URGENT Question about EXEC

Author

Topic

mori0043
Starting Member

18 Posts

Posted - 2004-10-26 : 14:00:23

Hello,

I am wondering if it is possible to retrieve the output of an EXEC call when passing it a SQL string from within a PROC?

EXAMPLE:

CREATE PROCEDURE graph
@securityGroup VARCHAR(40)
AS
DECLARE secID AS VARCHAR(10);
DECLARE secName AS VARCHAR(10);

BEGIN
-- I want to assign the result of the EXEC to @secID and @secName
EXEC('SELECT secId, secName FROM Security_Table WHERE securityGroup=''' + @securityGroup + ''')

Thanks in advance

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-26 : 14:21:45

Well I know this works:


DECLARE @SQLString nvarchar(1000),
	@ParamDef nvarchar(1000),
	@rtnVal nvarchar(1000)

SELECT @SQLString = 'SELECT @rtnVal = convert(varchar,au_id) From pubs.dbo.authors Where au_lname=''Blotchet-Halls'''
Select @ParamDef = '@rtnVal nvarchar(1000) OUTPUT'

EXEC dbo.sp_executesql @SQLString, @ParamDef, @rtnVal OUTPUT 

Select @rtnVal

And this should do it as well:



Create Table #blah (secId int, secName varchar(100))
Insert Into #blah
Exec('SELECT secId, secName FROM Security_Table WHERE securityGroup=''' + @securityGroup + ''')

Select @secId = secId, @secName = secName from #blah

drop table #blah

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-26 : 14:40:22

don't use EXEC. Not sure why you are thinking you need that. Dynamic SQL should be last resort.

This is as simple as:

CREATE PROCEDURE graph
@securityGroup VARCHAR(40)
AS
DECLARE secID AS VARCHAR(10);
DECLARE secName AS VARCHAR(10);

BEGIN
SELECT @secID = secId, @secName = secName
FROM Security_Table WHERE securityGroup=@securityGroup
...
END
- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-26 : 14:57:46

I agree with jeff but...
it may have been just an oversimplified example.

Corey

mori0043
Starting Member

18 Posts

Posted - 2004-10-26 : 14:59:18

Jeff,

I don't think that there is anyother way since I building a very complicated SQL query On the fly. Is Seventhnight's response the solution???

Let's say that I want to pass the database in dynamically and I also want to retrieve the output from the EXEC.

Ex:
EXEC('SELECT secId, secName FROM ' + @dbName + ' WHERE securityGroup=''' + @securityGroup + ''')

Is there a way to do this??

Thanks in advance

mori0043
Starting Member

18 Posts

Posted - 2004-10-26 : 15:01:33

Yes this was a simplified example. The sql dynamic statement that I pass in is much more complicated and needed.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-26 : 15:51:41

Have you tried my solutions?? One of them should do what you need.

Corey

mori0043
Starting Member

18 Posts

Posted - 2004-10-26 : 19:54:59

Yes it works. It is ashame that there is no easier way to assign the output to a variable, but nonetheless this will do the trick

Thanks!

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 22:09:34

Using sp_executesql also has the benefit that the [parameterised] query plan is cached - you should move as many variable parts of the query to the paramater list for sp_executesql as possible - but obviously not the table name!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources