using EXEC in a stored procedure

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

SQL Server Development (2000)

using EXEC in a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-05 : 10:55:25

Phill writes "hi, i have tried to return rows using this bit of code i got from 4GuysFromRolla.com:

----------------------------------------------------
Eric,

Yep, you can use the EXEC command to do this. In addition to prefixing stored procedure calls, this command can be used to execute a SQL statement in a string or a string variable. I've included a sample stored proc below...

CREATE PROC Foo
@stringpassed
AS
BEGIN
DECLARE @SQL varchar(100)
SET @SQL = 'SELECT * FROM table WHERE idno IN ' + @stringpassed

EXEC(@SQL)
END

You can find more information in SQL Server books online.

Sean

---------------------------------------------------------
I have 2 questions -
1. how do you return this to an ADODB recordset?
i have tried to simply do this:

Set rs= Cm.Execute

but the recordset remains closed
i noticed when running the stored procedure
no rows are returned.

i am using SQL 7.0 sp4, VB 6.0, Win2000

2. where can i find more info in the SQL server books online?
i cant seem to find any info on using EXEC within a
stored procedure"

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-05 : 11:03:11

Thats dynamic SQL your using their actually.. try looking it up.

For an ADODB record set... open it alsmot the same as you would with a select statement but instead of
select * from .... where column in '"&whatevaYaWant&"'"
you will use
exec FOO @stringpassed = '"&whatevaYaWant&"'"

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources