June 19, 2007 at 1:29 pm
I must be tired or something. Can anybody quickly point out my mistake here...I'm sure it's something silly
-- ================================================
-- usp_UpdateDataRefreshLog
-- ================================================
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 06/19/2007
-- Description: Updates the DW Refresh Data
-- =============================================
CREATE
PROCEDURE usp_UpdateDataRefreshLog
@TableName varchar
(30)
AS
SET
NOCOUNT ON
BEGIN
DECLARE @RecordCount int
DECLARE @Refreshdate DATETIME
-- Insert Data Refresh Information
SELECT @RecordCount = (SELECT count(*) FROM @TableName)
SELECT @RefreshDate = getdate()
UPDATE DataRefreshLog
SET RecordCount = @RecordCount,
LastRefreshDate
= @Refreshdate
WHERE
TableName
=@TableName
END
GO
Error: Msg 137, Level 15, State 2, Procedure usp_UpdateDataRefreshLog, Line 15
Must declare the variable '@TableName'.
June 19, 2007 at 2:06 pm
declare @countsql nvarchar(max)
set @countsql = N'select count(*) from ' + @TableName '
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 19, 2007 at 2:48 pm
Thanks I fixed my mistake. Here it is below.
-- ================================================
-- usp_UpdateDataRefreshLog
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 06/19/2007
-- Description: Updates the DW Refresh Data
-- =============================================
ALTER PROCEDURE usp_UpdateDataRefreshLog
@TableName varchar(30)
AS
SET NOCOUNT ON
BEGIN
DECLARE @RecordCount int
DECLARE @Refreshdate DATETIME
DECLARE @table_name sysname
DECLARE @myr int
SET @table_name=@TableName
DECLARE @cmd AS nvarchar(100)
SET @cmd = N'SELECT count(*) FROM ' + @table_name
-- Insert Data Refresh Information
EXEC sp_executesql
@stmt = @cmd,
@r = @myr OUTPUT
SET @RecordCount = (SELECT @myr)
SET @RefreshDate = getdate()
UPDATE DataRefreshLog
SET RecordCount = @RecordCount,
RefreshDate = @Refreshdate
WHERE
END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply