sp_executesql returns null - 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 2005 Forums

Transact-SQL (2005)

sp_executesql returns null

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2010-06-15 : 13:27:03

[code]
DECLARE @ParmDefinition nvarchar(4000),
@intRowCount int
SET @ParmDefinition='@intRowCount int OUTPUT';
SET NOEXEC ON;
EXECUTE sp_executesql 'Select 1',@ParmDefinition, @intRowCount OUTPUT;
SET NOEXEC OFF;
select @intRowCount
[/code]

How do I make the above return 1 instead of NULL?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 13:34:32

EXEC sp_executesql N'Select @intRowCount = 1', @ParmDefinition, @intRowCount=@intRowCount OUTPUT;

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

adlo
Posting Yak Master

108 Posts

Posted - 2010-06-16 : 11:22:56

I want it to return 1.


	DECLARE @ParmDefinition nvarchar(4000)
	DECLARE	@intRowCount	int
	SET @ParmDefinition='@intRowCount int OUTPUT';
	SET NOEXEC ON;
	EXEC sp_executesql N'Select @intRowCount = 1', @ParmDefinition, @intRowCount=@intRowCount OUTPUT;
	SET NOEXEC OFF;

returns NULL and I have to declare @intRowCount or get a must declare the variable message.

How do I make it work?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-16 : 11:28:39

I tested this and got 1 for @intRowCount:

DECLARE @ParmDefinition nvarchar(4000)
DECLARE	@intRowCount	int
SET @ParmDefinition='@intRowCount int OUTPUT';
EXEC sp_executesql N'Select @intRowCount = 1', @ParmDefinition, @intRowCount=@intRowCount OUTPUT;
SELECT @intRowCount

You won't get any results if SET NOEXEC is on.

adlo
Posting Yak Master

108 Posts

Posted - 2010-06-16 : 11:33:14

Thanks. Can't remember why I used NO EXEC it in the first place.

adlo
Posting Yak Master

108 Posts

Posted - 2010-06-16 : 12:55:46

I tried to use no exec for the below scenario to try resolve it but I can see it has no point as it always returns null.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146138

It is a different issue so I created a separate thread for it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources