Using Output Parameters with sp_executesql

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 Output Parameters with sp_executesql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-08 : 08:45:45

writes "Hi,

I have a problem storing output parameter from sp_executesql to a temp variable.

Below is the script which I am trying to excute.
Here the problem is @sql is not a sql statement, instead it is a dynamically generated function. It looks like
@sql = select dbo.getTier1Ratio('08551',200203) which returns a value.

DECLARE @name varchar(40)
DECLARE @docket varchar(5)
DECLARE @cycle int
DECLARE @sql nvarchar(512)
declare @out varchar(30)
DECLARE @ParmDefinition NVARCHAR(500)

set @docket = '08551'
set @cycle = 200203

Set @sql = N'select dbo.' + @name + '(''' + @docket + ''',' + cast(@cycle as nvarchar(6)) + ')'

SET @ParmDefinition=N'@parm1IN varchar(40),
@parm2IN varchar(5),
@parm3IN int,
@parm1OUT varchar(30) OUTPUT'

EXEC sp_executesql @sql,
@ParmDefinition,
@parm1IN = @name,
@parm2IN = @docket,
@parm3IN = @cycle,
@Parm1out = @out output

print @sql
print @out

Any help would be appreciated.

Thanks,
Reddy"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-08 : 09:00:53

The params in your @ParmDefinition variable must match the parms in your @sql string. And it doesn't look to me like you have any params in your @sql string (by the time it is built). Try this...


Set @sql = N'select @parm1OUT = dbo.' + @name +
      N'(''' + @docket + N''',' +
           cast(@cycle as nvarchar(6)) + N')' 

SET @ParmDefinition=N'@parm1OUT varchar(30) OUTPUT' 

EXEC sp_executesql
    @sql, 
    @ParmDefinition, 
    @out output

<O>

Edited by - Page47 on 07/08/2002 09:02:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources