Exception Handling in Dynamic SQL in SQL Server

Question

In Dynamic SQL statement one @STU_ID value is failing to insert the data into another table. Here I need to know which @STU_ID if failing when execution.

CREATE PROC DATA_COPY
 (
 @SRC_TABLE VARCHAR(30),
 @DEST_TABLE VARCHAR(30)
 )
 AS
 DECLARE @STU_ID INT
 BEGIN
  DECLARE STUDENT_CURSOR CURSOR FOR
  SELECT DISTINCT STU_ID FROM STUDENT

  OPEN STUDENT_CURSOR
        FETCH NEXT FROM STUDENT_CURSOR INTO @STU_ID
    WHILE @@FETCH_STATUS = 0
        BEGIN

EXEC ('INSERT INTO '+@DEST_TABLE+'
    SELECT *
    FROM '+@SRC_TABLE+' 
    WHERE STU_ID='+@STU_ID)

        FETCH NEXT FROM STUDENT_CURSOR INTO @STU_ID
    END
    CLOSE STUDENT_CURSOR
    DEALLOCATE STUDENT_CURSOR
 END

Can you help me how to do this ?

Thanks

Are you getting an error? If so can you include it? Most likely there is a case in which `SELECT * FROM '+@SRC_TABLE+' WHERE STU_ID='+@STU_ID` returns no results. You should check to make sure there is data before performing the insert. Also, you are inserting into a table based on SELECT *. This is going to give you hell when the schema changes. — Gibron, Apr 09 '13 at 06:37
The Error is: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. — user1990383, Apr 09 '13 at 06:40
I explicitly change the size of one column in destination table lower than the source table, in which my requirement is to get a message when i execute the Stored Procedure is "@STU_ID could't insert the data into destination table" — user1990383, Apr 09 '13 at 06:44
Your error seems to have nothing to do with the ID. You are trying to insert data into a column that is not large enough to store the data you have in your variable. Eg. your `@SRC_TABLE` column length's do not match `@DEST_TABLE`. See http://stackoverflow.com/questions/779082/sqlexception-string-or-binary-data-would-be-truncated — Gibron, Apr 09 '13 at 06:45

score 1 · Answer 1 · answered Apr 09 '13 at 07:04

You can capture data during the context of the error with TRY CATCH

BEGIN TRY
    EXEC ('INSERT INTO '+@DEST_TABLE+'
    SELECT *
    FROM '+@SRC_TABLE+' 
    WHERE STU_ID='+@STU_ID)
END TRY
BEGIN CATCH
    PRINT 'ERROR WITH VALUE @STU_ID = ' + CONVERT(VARCHAR,@STU_ID)
END CATCH

Exception Handling in Dynamic SQL in SQL Server

1 Answers1