April 28, 2016 at 9:29 am
Hi There,
PLEASE help me to figure out what's the issue is in following stored procedure! I am trying to pass source table name while running the SP and split all it's record equally into 4 fixed name tables.
Here is the SP.
USE [DMIGRATION]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE Doc124NEW_new (@DMIGRATIONNEW nvarchar(255))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @DMIGRATION nvarchar(255);
DECLARE @count int = 0;
DECLARE @numRows int = 0;
SELECT @DMIGRATION = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @DMIGRATIONNEW
SELECT @count = Select COUNT(*) FROM '+@DMIGRATION
SELECT @numRows = @count / 4
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql='SELECT TOP ('+ @numRows +') *
INTO [dbo].[DMIGRATIONnew1]
FROM '+@DMIGRATION
EXEC(@SQL)
DECLARE @sql1 AS NVARCHAR(MAX)
SELECT @sql1= 'SELECT TOP ('+@numRows+') *
INTO [dbo].[DMIGRATIONnew2]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP ('+@numRows+') [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL1)
DECLARE @sql2 AS NVARCHAR(MAX)
SELECT @sql2='SELECT TOP ('+@numRows+') *
INTO [dbo].[DMIGRATIONnew3]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP ('+@numRows+' * 2) [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL2)
DECLARE @sql3 AS NVARCHAR(MAX)
SELECT @sql3='SELECT *
INTO [dbo].[DMIGRATIONnew4]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP ('+@numRows+' * 3) [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL3)
END
April 28, 2016 at 9:46 am
So what is the issue? You have given us the code but told us nothing else about the problem.
April 28, 2016 at 9:58 am
I am getting following error.
Msg 245, Level 16, State 1, Procedure Doc124NEW_new, Line 36
Conversion failed when converting the varchar value 'SELECT TOP (' to data type int.
Here is my little modified SP again.
ALTER PROCEDURE Doc124NEW_new (@DMIGRATIONNEW nvarchar(255))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @DMIGRATION nvarchar(255);
DECLARE @count int = 0;
DECLARE @numRows int = 0;
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @sql1 AS NVARCHAR(MAX)
DECLARE @sql2 AS NVARCHAR(MAX)
DECLARE @sql3 AS NVARCHAR(MAX)
SELECT @DMIGRATION = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @DMIGRATIONNEW
--SELECT @count = COUNT(*) FROM '+@DMIGRATION+'
SELECT @count = p.rows
FROM sys.partitions p
JOIN sys.tables t
ON p.[object_id] = t.[object_id]
AND p.index_id < 2
WHERE OBJECT_NAME(t.[object_id]) = @DMIGRATION
SELECT @numRows = @count / 4
SELECT @sql='SELECT TOP ('+@numRows+') *
INTO [dbo].[DMIGRATIONnew1]
FROM '+@DMIGRATION
EXEC(@SQL)
SELECT @sql1= 'SELECT TOP '+@numRows+' *
INTO [dbo].[DMIGRATIONnew2]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+@numRows+' [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL1)
SELECT @sql2='SELECT TOP '+@numRows+' *
INTO [dbo].[DMIGRATIONnew3]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+@numRows+' * 2 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL2)
SELECT @sql3='SELECT *
INTO [dbo].[DMIGRATIONnew4]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+@numRows+' * 3 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL3)
END
April 28, 2016 at 10:03 am
You need to cast @NumRows as a character data type. At the moment, since it's int, it's attempting to add instead of concatenate.
John
April 28, 2016 at 10:03 am
Learner44 (4/28/2016)
I am getting following error.Msg 245, Level 16, State 1, Procedure Doc124NEW_new, Line 36
Conversion failed when converting the varchar value 'SELECT TOP (' to data type int.
Here is my little modified SP again.
ALTER PROCEDURE Doc124NEW_new (@DMIGRATIONNEW nvarchar(255))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @DMIGRATION nvarchar(255);
DECLARE @count int = 0;
DECLARE @numRows int = 0;
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @sql1 AS NVARCHAR(MAX)
DECLARE @sql2 AS NVARCHAR(MAX)
DECLARE @sql3 AS NVARCHAR(MAX)
SELECT @DMIGRATION = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @DMIGRATIONNEW
--SELECT @count = COUNT(*) FROM '+@DMIGRATION+'
SELECT @count = p.rows
FROM sys.partitions p
JOIN sys.tables t
ON p.[object_id] = t.[object_id]
AND p.index_id < 2
WHERE OBJECT_NAME(t.[object_id]) = @DMIGRATION
SELECT @numRows = @count / 4
SELECT @sql='SELECT TOP ('+@numRows+') *
INTO [dbo].[DMIGRATIONnew1]
FROM '+@DMIGRATION
EXEC(@SQL)
SELECT @sql1= 'SELECT TOP '+@numRows+' *
INTO [dbo].[DMIGRATIONnew2]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+@numRows+' [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL1)
SELECT @sql2='SELECT TOP '+@numRows+' *
INTO [dbo].[DMIGRATIONnew3]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+@numRows+' * 2 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL2)
SELECT @sql3='SELECT *
INTO [dbo].[DMIGRATIONnew4]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+@numRows+' * 3 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL3)
END
When you are building your dynamic SQL you need to cast the @numRows variable to string (varchar(30) perhaps).
April 28, 2016 at 10:04 am
You need to convert the @numRows to a varchar before trying to append it to the string.
Also it looks like you're just using TOP <x> rows on the main table, top doesn't guarantee that you'll get the same order every time so the multiple queries on the table inserting into each sub table might end up with duplicate records and might not result in all the records from the main table ending up in one of the sub tables.
April 28, 2016 at 10:15 am
Looks like that error is solved and got the new one! Neverending!
Error message
Invalid column name 'cindex'.
SP
ALTER PROCEDURE Doc124NEW_new (@DMIGRATIONNEW nvarchar(255))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DROP TABLE [dbo].[DMIGRATIONnew1]
DROP TABLE [dbo].[DMIGRATIONnew2]
DROP TABLE [dbo].[DMIGRATIONnew3]
DROP TABLE [dbo].[DMIGRATIONnew4]
DECLARE @DMIGRATION nvarchar(255);
DECLARE @count int = 0;
DECLARE @numRows int = 0;
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @sql1 AS NVARCHAR(MAX)
DECLARE @sql2 AS NVARCHAR(MAX)
DECLARE @sql3 AS NVARCHAR(MAX)
SELECT @DMIGRATION = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @DMIGRATIONNEW
--SELECT @count = COUNT(*) FROM '+@DMIGRATION+'
SELECT @count = p.rows
FROM sys.partitions p
JOIN sys.tables t
ON p.[object_id] = t.[object_id]
AND p.index_id < 2
WHERE OBJECT_NAME(t.[object_id]) = @DMIGRATION
SELECT @numRows = @count / 4
SELECT @sql='SELECT TOP ('+CAST(@numRows AS NVARCHAR(6))+') *
INTO [dbo].[DMIGRATIONnew1]
FROM '+@DMIGRATION
EXEC(@SQL)
SELECT @sql1= 'SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' *
INTO [dbo].[DMIGRATIONnew2]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL1)
SELECT @sql2='SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' *
INTO [dbo].[DMIGRATIONnew3]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' * 2 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL2)
SELECT @sql3='SELECT *
INTO [dbo].[DMIGRATIONnew4]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' * 3 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL3)
END
April 28, 2016 at 10:25 am
Learner44 (4/28/2016)
Looks like that error is solved and got the new one! Neverending!Error message
Invalid column name 'cindex'.
SP
ALTER PROCEDURE Doc124NEW_new (@DMIGRATIONNEW nvarchar(255))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DROP TABLE [dbo].[DMIGRATIONnew1]
DROP TABLE [dbo].[DMIGRATIONnew2]
DROP TABLE [dbo].[DMIGRATIONnew3]
DROP TABLE [dbo].[DMIGRATIONnew4]
DECLARE @DMIGRATION nvarchar(255);
DECLARE @count int = 0;
DECLARE @numRows int = 0;
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @sql1 AS NVARCHAR(MAX)
DECLARE @sql2 AS NVARCHAR(MAX)
DECLARE @sql3 AS NVARCHAR(MAX)
SELECT @DMIGRATION = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @DMIGRATIONNEW
--SELECT @count = COUNT(*) FROM '+@DMIGRATION+'
SELECT @count = p.rows
FROM sys.partitions p
JOIN sys.tables t
ON p.[object_id] = t.[object_id]
AND p.index_id < 2
WHERE OBJECT_NAME(t.[object_id]) = @DMIGRATION
SELECT @numRows = @count / 4
SELECT @sql='SELECT TOP ('+CAST(@numRows AS NVARCHAR(6))+') *
INTO [dbo].[DMIGRATIONnew1]
FROM '+@DMIGRATION
EXEC(@SQL)
SELECT @sql1= 'SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' *
INTO [dbo].[DMIGRATIONnew2]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL1)
SELECT @sql2='SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' *
INTO [dbo].[DMIGRATIONnew3]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' * 2 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL2)
SELECT @sql3='SELECT *
INTO [dbo].[DMIGRATIONnew4]
FROM '+@DMIGRATION+'
WHERE [cindex] NOT IN (SELECT TOP '+CAST(@numRows AS NVARCHAR(6))+' * 3 [cindex] FROM '+@DMIGRATION+')'
EXEC(@SQL3)
END
Look at the schema of the table(s) you are pulling the data from.
April 28, 2016 at 10:32 am
Not sure what you are trying to do, and why, but there is some basic things that may be able to be simplified, and, some basic logic flaws.
If the number of rows (@Count) variable is an odd number, and you divide by 4 to get the @numRows value, you will not get all of the rows.
Run this code, changing the value of @Count to whatever number you want.
In this example, it will miss 3 rows.
DECLARE @Count int = 1003
DECLARE @Divisor int = 4
SELECT @Count/@Divisor [@NumRows], (@Count/@Divisor) * @Divisor [SumOfParts], @Count - ((@Count/@Divisor) * @Divisor) [MissedRows]
This may not work properly without an ORDER BY clause. Order is not guaranteed.
WHERE [cindex] NOT IN (SELECT TOP '+@numRows+' [cindex] FROM '+@DMIGRATION+')'
Also, shouldn't this check if the rows are not in the NEW tables?
You do not need 4 different @SQL variables, one will suffice.
INFORMATION_SCHEMA is deprecated.
What is the purpose of this? I have this funny feeling that there is probably a simpler solution to this problem.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 28, 2016 at 10:38 am
All I am trying to do is to split the table records into 4 fixed name tables equally.
So all was working fine until source table name was hard coded, since there is requirement of passing source table as parameter this all started.
SO for an example my US-Employee table has 59000 rows, that it will split into 4 15000,15000,15000,14000 and will go into 4 different tables whose name will be always the same something like DMIGRATION1, DMIGRATION2, DMIGRATION3 and DMIGRATION4.
Hope I am clear in what I want to achieve.
April 28, 2016 at 10:45 am
This may work
ALTER PROCEDURE Doc124NEW_new
(
@DMIGRATIONNEW nvarchar(255)
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @ErrorMessage varchar(2048);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @Count int = 0
BEGIN TRY
BEGIN TRAN
--If there is no table, then do nothing
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @DMIGRATIONNEW) Begin
SELECT
@count = p.rows
FROM sys.partitions p
INNER JOIN sys.tables t ON p.[object_id] = t.[object_id]
AND p.index_id < 2
WHERE OBJECT_NAME(t.[object_id]) = @DMIGRATIONNEW
--First table
SET @SQL = 'SELECT TOP ('+ CONVERT(nvarchar(10), @count / 4) +') *
INTO [dbo].[DMIGRATIONnew1]
FROM ' + @DMIGRATIONNEW
EXEC(@SQL)
--Second table, where the rows are not already in the new table 1
SELECT @SQL = 'SELECT TOP ('+ CONVERT(nvarchar(10), @count / 4) +') *
INTO [dbo].[DMIGRATIONnew2]
FROM '+ @DMIGRATIONNEW +'
WHERE [cindex] NOT IN (SELECT [cindex] FROM DMIGRATIONnew1)'
EXEC(@SQL)
--Third table, where the rows are not already in the new table 1 and 2
SELECT @SQL = 'SELECT TOP ('+ CONVERT(nvarchar(10), @count / 4) +') *
INTO [dbo].[DMIGRATIONnew3]
FROM '+ @DMIGRATIONNEW +'
WHERE [cindex] NOT IN (SELECT [cindex] FROM DMIGRATIONnew1
UNION ALL
SELECT [cindex] FROM DMIGRATIONnew2)'
EXEC(@SQL)
--Fourth table, where the REMAINING rows are not already in the new table 1, 2, and 3
SELECT @SQL = 'SELECT *
INTO [dbo].[DMIGRATIONnew4]
FROM '+ @DMIGRATIONNEW +'
WHERE [cindex] NOT IN (SELECT [cindex] FROM DMIGRATIONnew1
UNION ALL
SELECT [cindex] FROM DMIGRATIONnew2
UNION ALL
SELECT [cindex] FROM DMIGRATIONnew3)'
EXEC(@SQL)
END
COMMIT TRAN
RETURN 0
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0 Begin
ROLLBACK TRANSACTION
End
SELECT
@ErrorMessage = 'Procedure: ' + ISNULL(ERROR_PROCEDURE(), OBJECT_NAME(@@PROCID)) + ' Message: ' + ERROR_MESSAGE() + ' Line Number: ' + CONVERT(varchar(20), ERROR_LINE()),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
RETURN -1
END CATCH
Go
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 28, 2016 at 11:07 am
Only one question now!!
How I can become like you pro in SQL
Please answer. I am ready to do whatever it takes.
BY the way that solution worked fine.
April 28, 2016 at 11:10 am
An alternative to John's nice query.
ALTER PROCEDURE Doc124NEW_new (@DMIGRATIONNEW nvarchar(128)) --Objects can only have 128 characters. Could use sysname instead.
AS
SET NOCOUNT ON;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @SQL_Base nvarchar(max),
@SQL nvarchar(max);
SET @SQL_Base = N'
WITH CTE AS(
SELECT *, NTILE(4) OVER( ORDER BY ' + STUFF((SELECT ',' + QUOTENAME(c.name)
FROM sys.Columns c
JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE OBJECT_NAME(c.object_id) = @DMIGRATIONNEW
AND i.is_primary_key = 1
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
+ ') nt
FROM ' + QUOTENAME( @DMIGRATIONNEW) + '
)
SELECT ' +
STUFF((
SELECT ',' + QUOTENAME(name)
FROM sys.Columns c
WHERE OBJECT_NAME(object_id) = @DMIGRATIONNEW
ORDER BY column_id
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
+ '
INTO [dbo].[DMIGRATIONnew<<number>>]
FROM CTE
WHERE nt = <<number>>;';
SET @SQL = REPLACE( @SQL_Base, '<<number>>', '1');
EXEC( @SQL);
SET @SQL = REPLACE( @SQL_Base, '<<number>>', '2');
EXEC( @SQL);
SET @SQL = REPLACE( @SQL_Base, '<<number>>', '3');
EXEC( @SQL);
SET @SQL = REPLACE( @SQL_Base, '<<number>>', '4');
EXEC( @SQL);
GO
I'm using a concatenation method explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
I also changed your parameter length and used some options to have the correct id column(s).
EDIT: Forgot to use parameters for the table name.
April 28, 2016 at 11:13 am
Learner44 (4/28/2016)
Only one question now!!How I can become like you pro in SQL
Please answer. I am ready to do whatever it takes.
BY the way that solution worked fine.
Read blogs, articles and books, install SQL Server on your local machine (Developer edition is now free), practice and try to answer forum posts (even if you don't post your answers or if there's already an answer). Appreciate the feedback on all code you post.
April 28, 2016 at 11:31 am
Michael L John (4/28/2016)
This may work
ALTER PROCEDURE Doc124NEW_new
(
@DMIGRATIONNEW nvarchar(255)
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @ErrorMessage varchar(2048);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @Count int = 0
BEGIN TRY
BEGIN TRAN
--If there is no table, then do nothing
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @DMIGRATIONNEW) Begin
SELECT
@count = p.rows
FROM sys.partitions p
INNER JOIN sys.tables t ON p.[object_id] = t.[object_id]
AND p.index_id < 2
WHERE OBJECT_NAME(t.[object_id]) = @DMIGRATIONNEW
--First table
SET @SQL = 'SELECT TOP ('+ CONVERT(nvarchar(10), @count / 4) +') *
INTO [dbo].[DMIGRATIONnew1]
FROM ' + @DMIGRATIONNEW
EXEC(@SQL)
--Second table, where the rows are not already in the new table 1
SELECT @SQL = 'SELECT TOP ('+ CONVERT(nvarchar(10), @count / 4) +') *
INTO [dbo].[DMIGRATIONnew2]
FROM '+ @DMIGRATIONNEW +'
WHERE [cindex] NOT IN (SELECT [cindex] FROM DMIGRATIONnew1)'
EXEC(@SQL)
--Third table, where the rows are not already in the new table 1 and 2
SELECT @SQL = 'SELECT TOP ('+ CONVERT(nvarchar(10), @count / 4) +') *
INTO [dbo].[DMIGRATIONnew3]
FROM '+ @DMIGRATIONNEW +'
WHERE [cindex] NOT IN (SELECT [cindex] FROM DMIGRATIONnew1
UNION ALL
SELECT [cindex] FROM DMIGRATIONnew2)'
EXEC(@SQL)
--Fourth table, where the REMAINING rows are not already in the new table 1, 2, and 3
SELECT @SQL = 'SELECT *
INTO [dbo].[DMIGRATIONnew4]
FROM '+ @DMIGRATIONNEW +'
WHERE [cindex] NOT IN (SELECT [cindex] FROM DMIGRATIONnew1
UNION ALL
SELECT [cindex] FROM DMIGRATIONnew2
UNION ALL
SELECT [cindex] FROM DMIGRATIONnew3)'
EXEC(@SQL)
END
COMMIT TRAN
RETURN 0
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0 Begin
ROLLBACK TRANSACTION
End
SELECT
@ErrorMessage = 'Procedure: ' + ISNULL(ERROR_PROCEDURE(), OBJECT_NAME(@@PROCID)) + ' Message: ' + ERROR_MESSAGE() + ' Line Number: ' + CONVERT(varchar(20), ERROR_LINE()),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
RETURN -1
END CATCH
Go
I just added 4 drop statements before sp , because every time run this sp i don't care about rpeviously done tables.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply