Problem with sp_executesql - 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)

Problem with sp_executesql

Author

Topic

galavezh
Starting Member

6 Posts

Posted - 2011-07-30 : 05:26:59

Hi,
I have a Problem with This Statment:
Alter PROCEDURE SpName (@TableNames nvarchar(500))
AS
BEGIN
declare @NumberOut int
EXECUTE sp_executesql N'Select @Number=count(*) From @TableName',N'@TableName nvarchar(500)',N'@Number int Output',@TableName=@TableNames,@Number=@NumberOut Output
select @NumberOut
END
GO

Please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 05:36:25

Try:-


Alter PROCEDURE SpName (@TableNames nvarchar(500))
AS
BEGIN
declare @NumberOut int
EXECUTE sp_executesql N'Select @Number=count(*) From @TableName',N'@TableName nvarchar(500),@Number int Output',@TableName=@TableNames,@Number=@NumberOut Output
select @NumberOut
END
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

galavezh
Starting Member

6 Posts

Posted - 2011-07-30 : 05:41:40

this code has a error:
Alter PROCEDURE SpName (@TableNames nvarchar(500))
AS
BEGIN
declare @NumberOut int
EXECUTE sp_executesql N'Select @Number=count(*) From @TableName',N'@TableName nvarchar(500),@Number int Output',@TableName=@TableNames,@Number=@NumberOut Output
select @NumberOut
END
GO

the error is:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TableName".

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-30 : 06:15:28

[code]
CREATE PROCEDURE SpName (@TableNames nvarchar(500))
AS
BEGIN
declare @sql nvarchar(max),
@NumberOut int

select @sql = N'Select @Number=count(*) From ' + @TableNames
EXECUTE sp_executesql @sql,
N'@Number int Output',
@Number = @NumberOut Output
select @NumberOut
END
GO
[/code]

KH
[spoiler]Time is always against us[/spoiler]

galavezh
Starting Member

6 Posts

Posted - 2011-07-30 : 23:45:37

Thanks a lot khtan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources