sp_executesql problem - 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

SQL Server Development (2000)

sp_executesql problem

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2004-10-14 : 06:34:48

I have a problem with this code:

declare @table varchar(50), @count int
exec sp_executesql N'set @count = (select count(*) from @table)',
N'@count int, @table varchar50)',@count,@table='tablex'

I keep getting the error:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@table'.

any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-14 : 06:49:36

this should fix it:

declare @table table(varchar(50), @count int)

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

iminore
Posting Yak Master

141 Posts

Posted - 2004-10-14 : 06:54:09

I don't think so

Kristen
Test

22859 Posts

Posted - 2004-10-14 : 06:57:49

declare @table varchar(50), @count int
exec sp_executesql N'set @count = (select count(*) from ' + @table + ')',
N'@count int~~, @table varchar50~~)',@count~~,@table='tablex'~~

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-14 : 07:02:00

yeah every now and then i must post a stupid answer.... sorry about that iminore...
serves me right for not reading the question correctly...

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

iminore
Posting Yak Master

141 Posts

Posted - 2004-10-14 : 07:25:56

Kristen, thanks for that - it's a step in the right direction.

I don't know if you have the patience for this but I'm trying to write a SP to get the number of records in all tables of a database:

CREATE PROCEDURE [dbo].[KASPTableRecords]
AS
create table #tables (tblname varchar(50), norecs int)
insert into #tables select name,0 from sysobjects where xtype = 'U'
declare @table varchar(50), @mtable varchar(50), @count int, @str nvarchar(400)
set @mtable = ''
set @table = (select min(tblname) from #tables where tblname > @mtable)
while @table is not null
begin
set @str = 'set @count = (select count(*) from ' + @table + ')'
exec sp_executesql @str, N'@count int',@count
update #tables set norecs = @count where tblname = @table
set @mtable = @table
set @table = (select min(tblname) from #tables where tblname > @mtable)
end

select * from #tables order by tblname
GO

and I'm getting nulls as record count for all the tables.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-14 : 07:29:24

then read this:
http://www.nigelrivett.net/sp_GetRowsForAllTables.html

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

iminore
Posting Yak Master

141 Posts

Posted - 2004-10-14 : 08:16:59

discovered the problem - need 'output' with the parameter definitions when using sp_executesql

reckon this is better than the link you suggested spirit1 - it doesn't use sysindexes - which can be wrong.

Kristen
Test

22859 Posts

Posted - 2004-10-14 : 09:20:24

Is the #tables in scpe from the inner EXEC I wonder?

if so you could probably just do:

set @str = 'update #tables set norecs = (select count(*) from ' + @table + ') where tblname = ' + @table
exec(@str)

I am ashamed to say that my SProc to do this particular trick uses a cursor ...

Kristen

iminore
Posting Yak Master

141 Posts

Posted - 2004-10-14 : 09:36:21

Kristen

it didn't like that, one day I'll understand scope

Kristen
Test

22859 Posts

Posted - 2004-10-14 : 09:45:58

I did think that might be a problem. We use a permanent table, thus no problem with scope.

FWIW We run our "RowCountAllTables" once a day, and store the results with the date/time so we have a history over time.

Kristen

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-10-14 : 11:23:10

how about:


create table #tables (tblname varchar(50), norecs int)
exec sp_msforeachtable @command1='insert into #tables select ''?'',count(*) from ?'
select * from #tables order by tblname

I think Kristen's extention to add the getdate() is a very valuable one, if the table is permanent. Then the code in your proc would only be:


exec sp_msforeachtable @command1='insert into #tables (tblname,norecs) select ''?'',count(*) from ?'

Although, admittedly, SP_MSForeachTable does use a cursor.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources