SQL Server: stored procedure not working, but works outside of stored procedure

Question

SQL Server: the following stored procedure is not working, but the SQL statement works outside of stored procedure.

CREATE PROCEDURE schema1.dropConstraints
      (@schemaName AS nvarchar, @tableName AS nvarchar) 
AS
BEGIN
    DECLARE @cname nvarchar(80)
    DECLARE @sqlStatement nvarchar(100)

    DECLARE myCursor CURSOR LOCAL FOR
        SELECT constraint_name 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE constraint_schema = @schemaName 
          AND table_name = @tableName;

    OPEN myCursor;
    FETCH NEXT FROM myCursor INTO @cname;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sqlStatement = 'ALTER TABLE ' + @schemaName + '.' + @tableName + ' DROP CONSTRAINT ' + @cname;
        EXEC sp_executesql @sqlStatement;

        FETCH NEXT FROM myCursor INTO @cname;
    END;

    CLOSE myCursor;
    DEALLOCATE myCursor;
END;

Calling stored procedure

exec schema1.dropConstraints 'schema1', 'Foo';

Table Foo's constraints (PK, FK) were not removed. No errors.

But running the code outside stored procedure, it works fine.

    DECLARE @schemaName nvarchar(80) = 'schema1';
    DECLARE @tableName nvarchar(80) = 'Foo';

    DECLARE @cname nvarchar(80)
    DECLARE @sqlStatement nvarchar(100)

    DECLARE myCursor CURSOR LOCAL FOR 
        SELECT constraint_name 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE constraint_schema = @schemaName AND table_name = @tableName;

    OPEN myCursor;
    FETCH NEXT FROM myCursor INTO @cname;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sqlStatement = 'ALTER TABLE ' + @schemaName + '.' + @tableName + ' DROP CONSTRAINT ' + @cname;
        EXEC sp_executesql @sqlStatement;

        FETCH NEXT FROM myCursor INTO @cname;
    END;

    CLOSE myCursor;
    DEALLOCATE myCursor;

This works. Table Foo's constraints (PK, FK) were removed. What is the difference?

login: sa.

Although probably not the cause of the issue, the cursor name is not consistent between the `OPEN` and `CLOSE` and `DEALLOCATE` statements (`myCursor` vs `c`). — Ed Harper, Mar 14 '20 at 10:12
Side notes: For identifies like schema or table names use `sysname`. Do not use unquoted identifiers in the query string. It's special datatype specifically for identifiers of objects. And use `quotename()` to quote them. Otherwise you'll run into problem if the identifiers given to your procedure aren't simple ones or even maliciously malformed thus enabling injection. — sticky bit, Mar 14 '20 at 10:18
You didn't specify a length for the `nvarchar` data type parameters. The default length is 1 so the specified value is truncated. Personally, I'd use `sysname` like @stickybit suggested, which is a synonym for `nvarchar(128)` and aligns with SQL Server object and column names. — Dan Guzman, Mar 14 '20 at 10:43
add `PRINT @sqlStatement` to check whether NVARCHAR length is the issue — devio, Mar 14 '20 at 18:19
PRINT not working inside stored procedure. tried, but no messages are printed. — eastwater, Mar 14 '20 at 19:28

score 2 · Answer 1 · answered Mar 14 '20 at 12:08

The maximum character lengths are missing from these parameter data type declarations:

CREATE PROCEDURE schema1.dropConstraints
      (@schemaName AS nvarchar, @tableName AS nvarchar)

Because the default length is 1, the supplied values are silently truncated to a single character and the results are not as expected.

It's a good practice to ensure parameter data types and lengths match those of referenced columns. In the case of the INFORMATION_SCHEMA views, consult the documentation and you'll find the appropriate data type is nvarchar(128). Personally, I like to use sysname for identifier types in SQL Server, which is a synonym for nvarchar(128).

CREATE PROCEDURE schema1.dropConstraints
      @schemaName AS sysname, @tableName AS sysname

Thanks. changing datatype to sysname works. But constraints starting with "DF_" not removed. What are these constraints? I did not add these explicitly. — eastwater, Mar 14 '20 at 18:07
Judging by the name, those looks like default constraints. See [this answer](https://stackoverflow.com/questions/141682/how-do-i-find-a-default-constraint-using-information-schema). Consider using the catalog views instead as those will expose all SQL Server features, albeit proprietary. — Dan Guzman, Mar 14 '20 at 18:19

SQL Server: stored procedure not working, but works outside of stored procedure

1 Answers1