Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Save The Newbie! SQL help

    Hey guys. I have been given the task of converting from oracle to microsoft SQL syntax.

    I have this oracle code:
    CREATE OR REPLACE PROCEDURE BA_DROP_TABLE (p_TableName IN varchar2) IS
    i int;
    BEGIN
    select count(*) into i from user_tables where table_name = p_TableName;
    IF i=1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE ' || p_TableName || ' CASCADE CONSTRAINTS';
    END IF;
    END;
    /

    I tried to convert it, and this is what I came up with:
    if (object_id ('BA_DROP_TABLE') is not null)
    drop proc BA_DROP_TABLE
    go

    CREATE PROCEDURE BA_DROP_TABLE @p_TableName varchar(30)
    AS
    DECLARE @i integer
    BEGIN
    select @i = count(*) from user_tables where table_name = p_TableName
    IF @i=1
    DROP TABLE p_TableName
    END

    This doesn't work. I think the problem has something to do with not being able to drop the table because its a parameter. I've heard talk of dynamic sql.

    Someone PLEASE help a guy out!

    Thanks

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    replace "DROP TABLE p_TableName" with "exec ('drop table ' + @p_TableName)

    Make sure the user has DROP OBJECTS permission.

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Thanks for reply

    Now I get this error:

    Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'BA_DROP_TABLE'. The stored procedure will still be created.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •