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
select count(*) into i from user_tables where table_name = p_TableName;
IF i=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || p_TableName || ' CASCADE CONSTRAINTS';
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
CREATE PROCEDURE BA_DROP_TABLE @p_TableName varchar(30)
DECLARE @i integer
select @i = count(*) from user_tables where table_name = p_TableName
DROP TABLE p_TableName
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.