| Author |
 Topic  |
|
|
Ally
Starting Member
10 Posts |
 Posted - 07/19/2012 : 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
India
47023 Posts |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/19/2012 : 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
India
47023 Posts |
Posted - 07/19/2012 : 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 - 07/19/2012 : 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
India
47023 Posts |
Posted - 07/19/2012 : 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 - 07/19/2012 : 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
India
47023 Posts |
Posted - 07/19/2012 : 14:49:24
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Using Table name as a parameter
