sp_executesql and OUTPUT - 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)

sp_executesql and OUTPUT

Author

Topic

mem
Starting Member

28 Posts

Posted - 2004-10-18 : 15:06:30

Hello,

I'm trying to return an output value from a procedure that is using the sp_executesql procedure. For example...

create PROCEDURE dbo.Bogus_Procedure

@varTableName nvarchar(100),
@varBogusOutPut nvarchar(8) OUTPUT

AS
BEGIN
SET NOCOUNT ON

DECLARE @SQLString nvarchar(4000)

SELECT @SQLString = 'SELECT ' + @varBogusOutPut + ' = Column_One ' +
'FROM ' + @varTableName + ' ' +
'WHERE Column_One = 1'

EXEC dbo.sp_executesql @SQLString

SET NOCOUNT OFF
END

@varBogusOutPut would obviously return NULL...

So, How can I tweak this so that I get my value? Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-18 : 16:13:37

This should be what you are working on...


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

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 16:13:52

well you need to set @varBogusOutPut to something somewhere in your sproc...
set @varBogusOutPut = someValueOrSomethingElse

Go with the flow & have fun! Else fight the flow

mem
Starting Member

28 Posts

Posted - 2004-10-18 : 16:45:46

Thanks Corey worked perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources