Stored Procedure and dynamic SQL

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)

Stored Procedure and dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-22 : 07:38:15

George writes "SQL server 2000 sp3


CREATE PROCEDURE gspdMax 
@dmax int OUTPUT
AS
BEGIN
	declare @wdbn varchar(80)
	declare @cmdline varchar(800)
        --get database name with UDF
	set @wdbn = ( dbo.gspwDBN() + '.dbo.sys_cfg')
        --build select stmt
	set  @cmdline = ("(select [value] from " + RTRIM(@securitydbn) + " where cfg_varname='sys_dcd_levels')")
	exec (@cmdline )
        --HOW DO I ACCOMPLISH THIS:
        --set @dmax = (EXEC(@cmdline))

GO

In the above procedure, in database A, I obtain the name of
another database.

EXEC does not like the use of RTRIM, so I build @cmdline
then run EXEC which in isqlw.exe returns one value from the
select.

How do I return this value in the output variable @dmax?

EXEC cannot set a declared variable because it thinks
it's not declared.

User Defined Functions don't allow EXEC with strings.
"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources