Using Table name as a parameter

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

General SQL Server Forums

New to SQL Server Programming

Using Table name as a parameter

Author

Topic

Ally
Starting Member

10 Posts

Posted - 2012-07-19 : 09:02:26

Hello Everyone,

I am trying to use table name as a parameter.

everything works in this code EXCEPT @Curr_Row_Count parameter. I am getting error for this parameter.

Can anyone tell me why am I getting error?

Any help is greatly appreciated,

**********Here is the code*********

Declare @weekday int
Declare @bussday int
Set @weekday = (SELECT DATEPART(WEEKDAY, (select today from CCFADM.dbo.cca_today) ))
set @bussday = (Select CCSBDM.dbo.f_isValidBusinessDay((select today from CCFADM.dbo.cca_today),'Eastern'))

IF @weekday IN (2,3,4,5,6) and @bussday = 1
Begin
Select * Into #Temp From T_COMG_FINAL_TABLES
While (Select Count(*) From #Temp) > 0
Begin
Declare @table_name NVarchar(60)
Declare @Prev_Row_Count int
Declare @Curr_Row_Count nvarchar(10)
DECLARE @TSQL varchar(5000)
DECLARE @TSQL1 varchar(5000)
DECLARE @execquery AS NVARCHAR(MAX)
Declare @RunDay_date DateTime
Declare @jobid int

Select Top 1 @table_name = table_name ,@jobid=JobId From #Temp

Set @RunDay_date = CONVERT(DATETIME,LEFT(CONVERT(VARCHAR, getdate(), 120), 10))
Set @TSQL = 'Set ' + @Curr_Row_Count + ' = (Select count(*) as cnt from ' + @table_name + ')'

Select @Prev_Row_Count=ISNULL(Current_Row_Count,0) from dbo.T_COMG_JOBS_TABLES
where table_name = @table_name and RunDay_date = (Select Max(RunDay_date) from T_COMG_JOBS_TABLES where table_name = @table_name)
Set @TSQL = 'Select count(*) as cnt from ' + @table_name
Set @Curr_Row_Count = exec (@TSQL)

INSERT INTO [dbo].[T_COMG_JOBS_TABLES]
([RunDay_Date]
,[Prev_Row_Count]
,[Current_Row_Count]
,[JobID]
,[Table_name])
VALUES
(@RunDay_date
,@Prev_Row_Count
,@Curr_Row_Count
,@jobid
,@table_name)

Delete #Temp Where table_name = @table_name

End

END
ELSE
BEGIN
print 'Do something else'
END

drop table #temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 09:39:30

for getting return values using dynamic sql you should be using sp_executesql rather than EXEC

see

http://support.microsoft.com/kb/262499

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-19 : 09:42:08

see
http://www.nigelrivett.net/SQLTsql/sp_executeSQL.html

Set @TSQL = 'Select @Curr_Row_Count = count(*) from ' + @table_name
exec sp_executesql @TSQL, N'@Curr_Row_Count int output', @Curr_Row_Count output

you could also
select @Curr_Row_Count = rows
from sysindexes
where id = object_id(@table_name)
and indid in (0,1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 09:43:56

also you might have to change datatype of @Curr_Row_Count to int rather than nvarchar(10) as it stores a count

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

Ally
Starting Member

10 Posts

Posted - 2012-07-19 : 11:21:04

Hi,
Thanks for reply.
I have updated my code as below. But I am getting null value for @Curr_Row_Count.

Declare @weekday int
Declare @bussday int
Set @weekday = (SELECT DATEPART(WEEKDAY, (select today from CCFADM.dbo.cca_today) ))
set @bussday = (Select CCSBDM.dbo.f_isValidBusinessDay((select today from CCFADM.dbo.cca_today),'Eastern'))

IF @weekday IN (2,3,4,5,6) and @bussday = 1
Begin
Select * Into #Temp From T_COMG_FINAL_TABLES_TST
While (Select Count(*) From #Temp) > 0
Begin
Declare @table_name NVarchar(60)
Declare @Prev_Row_Count int
Declare @Curr_Row_Count int
DECLARE @TSQL AS NVARCHAR(MAX)
DECLARE @TSQL1 AS NVARCHAR(MAX)
DECLARE @execquery AS NVARCHAR(MAX)
Declare @RunDay_date DateTime
Declare @jobid int

Select Top 1 @table_name = table_name ,@jobid=JobId From #Temp

Set @RunDay_date = CONVERT(DATETIME,LEFT(CONVERT(VARCHAR, getdate(), 120), 10))

Select @Prev_Row_Count=ISNULL(Current_Row_Count,0) from dbo.T_COMG_JOBS_TABLES_TST
where table_name = @table_name and RunDay_date = (Select Max(RunDay_date) from T_COMG_JOBS_TABLES_TST where table_name = @table_name)

Set @TSQL = 'Select count(*) as cnt from ' + @table_name
exec sp_executesql @TSQL, N'@Curr_Row_Count int output', @Curr_Row_Count output

INSERT INTO [dbo].[T_COMG_JOBS_TABLES_TST]
([RunDay_Date]
,[Prev_Row_Count]
,[Current_Row_Count]
,[JobID]
,[Table_name])
VALUES
(@RunDay_date
,@Prev_Row_Count
,@Curr_Row_Count
,@jobid
,@table_name)

Delete #Temp Where table_name = @table_name

End

END
ELSE
BEGIN
print 'Do something else'
END

drop table #temp

Thanks
Ally

quote:
Originally posted by nigelrivett

see
http://www.nigelrivett.net/SQLTsql/sp_executeSQL.html

Set @TSQL = 'Select @Curr_Row_Count = count(*) from ' + @table_name
exec sp_executesql @TSQL, N'@Curr_Row_Count int output', @Curr_Row_Count output

you could also
select @Curr_Row_Count = rows
from sysindexes
where id = object_id(@table_name)
and indid in (0,1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 11:32:12

you have not included variable inside dynamic sql string


Set @TSQL = 'Select @Curr_Row_Count = count(*) as cnt from ' + @table_name 
exec sp_executesql @TSQL, N'@Curr_Row_Count int output', @Curr_Row_Count=@Curr_Row_Count  output

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

Ally
Starting Member

10 Posts

Posted - 2012-07-19 : 14:11:48

It works. thanks everyone. Really appreciate it!

quote:
Originally posted by visakh16

you have not included variable inside dynamic sql string

Set @TSQL = 'Select @Curr_Row_Count = count(*) as cnt from ' + @table_name
exec sp_executesql @TSQL, N'@Curr_Row_Count int output', @Curr_Row_Count=@Curr_Row_Count output

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 14:49:24

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources