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

udayfn12
Starting Member

15 Posts

Posted - 2002-07-02 : 18:14:49

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-03 : 07:46:39

See www.nigelrivett.co.uk for info on how to use sp_executesql

In this query you don't have anything which sets the value for the output parameter.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

udayfn12
Starting Member

15 Posts

Posted - 2002-07-03 : 09:43:11

Hi Nr,

Thanks for your reply, I need some more clarification on this.
Let me put my question this way. How do I excute a dynamically generated Function? In the above statements, @name is the function and
@sql is set to excute dynamically generated function. The prepared statement @sql looks like this
select dbo.getTier1Ratio('00007',200203). I am able to excute this using sp_excutesql but not able to store the o/p to a variable.

Thanks in advance.

See www.nigelrivett.co.uk for info on how to use sp_executesql

In this query you don't have anything which sets the value for the output parameter.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
[/quote]

Edited by - udayfn12 on 07/03/2002 09:56:02

udayfn12
Starting Member

15 Posts

Posted - 2002-07-03 : 09:43:20

Hi Nr,

Thanks for your reply, I need some more clarification on this.
Let me put my question this way. How do I excute a dynamically generated Function? In the above statements, @name is the function and
@sql is set to excute dynamically generated function.

Thanks in advance.

See www.nigelrivett.co.uk for info on how to use sp_executesql

In this query you don't have anything which sets the value for the output parameter.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
[/quote]

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-03 : 19:45:40

try setting the output parameter in the select statement

select @parm = func()

What you have just returns a result set - it doesn't set the value of the parameter.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources