Harreni
asked on
T-SQL to Update Table Dynamically
Hi Experts,
I n step #2 In the following SP, I tried to update the table called @AsIsTable but I keep getting this error:
Msg 1087, Level 16, State 1, Procedure sp_ImportingNewDump_1.0_Co nslidated, Line 37 [Batch Start Line 0]
Must declare the table variable "@AsIsTable".
So how to fix that step?
Thanks a lot in advance.
Harreni
I n step #2 In the following SP, I tried to update the table called @AsIsTable but I keep getting this error:
Msg 1087, Level 16, State 1, Procedure sp_ImportingNewDump_1.0_Co
Must declare the table variable "@AsIsTable".
So how to fix that step?
CREATE PROCEDURE [dbo].[sp_ImportingNewDump_1.0_Conslidated]
-- Add the parameters for the stored procedure here
@DumpDate nvarchar(255) = NULL
-----------------------------------------------------------------------------------------------------
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @AsIsTable varchar(MAX);
DECLARE @CreateAsIsTableSQL varchar(MAX);
DECLARE @VersionNumber nvarchar(255);
DECLARE @VersionDate nvarchar(255);
DECLARE @DataSourceName nvarchar(255);
DECLARE @ImportingDate nvarchar(255);
SET NOCOUNT ON;
BEGIN TRAN
BEGIN TRY
-----------------------------------------------------------------------------------------------------
-- 1. Create AsIs Table
SET @AsIsTable = N'tb_Test_Services_ImportedByWizard_AsIs_'+@DumpDate+''
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @AsIsTable)
PRINT 'Table ' + @AsIsTable + ' already exist'
ELSE
SET @CreateAsIsTableSQL = 'CREATE TABLE ' + @AsIsTable + ' (
[AgencyName] [nvarchar](255) NULL,
[ImportingDate] [nvarchar](255) NULL,
[VersionNumber] [nvarchar](50) NULL,
[VersionDate] [nvarchar](255) NULL,
[DataSourceName] [nvarchar](255) NULL,
) ON [PRIMARY]';
Execute (@CreateAsIsTableSQL);
-----------------------------------------------------------------------------------------------------
-- 2. Update AsIs Table
UPDATE @AsIsTable
SET
VersionNumber = @VersionNumber
,VersionDate = @VersionDate
,DataSourceName = @DataSourceName
,ImportingDate = @ImportingDate
-----------------------------------------------------------------------------------------------------
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
-----------------------------------------------------------------------------------------------------
END
Thanks a lot in advance.
Harreni
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot experts for your fast response and explanation.
Open in new window