"anchored declaration" in T-SQL?

[alfredjp]

coming from an oracle background, im quite having lots of gripes (recently) trying to get things to work in SQL Server, and i would really appreciate help/some advice.

recently, ive come across this article comparing oracle's pl/sql and sql server's t-sql:

http://www.devx.com/dbzone/Article/11363/0/page/1

and i would love if someone could show to implement "anchored declarations".

to quote from the article:

---

To gain an understanding of how PL/SQL can ease your coding, consider the following—unfortunately, common—scenario. Suppose you have a column in a table that is declared as a char (50). In all your stored procedures that reference this column and store values from it in a variable, you also declare your variables to be char (50). Now, after you've finished your umpteenth procedure, you find out that the column needs to be extended to a char (100). In T-SQL, you (or in fact, me during a very long afternoon session several weeks ago) are in for a lot of searching and replacing the char (50) declarations with char (100). But in PL/SQL, you can simply use what's called an anchored declaration and avoid the problem.

An anchored declaration allows you to declare your variable as being of whatever type the particular column in the table is. For example, emp_id emp.empno%TYPE declares a variable called emp_id, which is the same type as the column empno in the emp table. If I change the column type in the table, all I need to do is recompile the dependant code (Oracle also keeps track of the dependencies for you and marks the code as "invalid&quot. Oracle makes coding even easier by enabling you to declare in one fell swoop all the variables needed to represent a row in a table. The emprow emp%ROWTYPE command declares a structure emprow that has the same attributes as the columns in the emp table.

---

thanks!!!

 

[SemperFiDownUnda]

You have pro's and con's with each database.

For your problem if you want to know what stored procedures depends on your object check sp_depends. Boom there is your list.

On the bright side if you want to select records back to ADO in SQL server you don't need to build cursors. You also don't need to use dynamic SQL when you have variables you want to use in a SQL statement.

Working with both I perfer how easy SQL server is to impliment stuff in. I still get frustrated that in Oracle table and column names are uppercase or if they aren't you have to use quoted identifiers for them

take this UDF in Oracle that they are using here so that they don't have to use a oracle sequence ( don't get me started on this...I don't like it )

FUNCTION GETNEXTID (p_TableName IN VARCHAR2, p_NextVal OUT NUMBER)
RETURN NUMBER IS
vCurrval sequences.currval%type;
sSql VARCHAR2(100);
vCursor integer;
vDummy integer;
BEGIN
   vCursor := DBMS_SQL.OPEN_CURSOR;
   sSql := 'select currval from sequences SEQ where SEQ.NAME = '''|| p_TableName ||'''' || ' for update';
   DBMS_SQL.PARSE(vCursor, sSql, DBMS_SQL.native);
   DBMS_SQL.DEFINE_COLUMN(vCursor, 1, vCurrval);
   vDummy := DBMS_SQL.EXECUTE(vCursor);
   if DBMS_SQL.FETCH_ROWS(vCursor) != 0 then
     DBMS_SQL.COLUMN_VALUE(vCursor, 1, vCurrval);
     vCurrval := vCurrval + 1;
	 sSql := 'update sequences set CurrVal = :v1 where NAME = '''|| p_TableName ||'''';
	 DBMS_SQL.PARSE(vCursor, sSql, DBMS_SQL.native);
	 DBMS_SQL.BIND_VARIABLE(vCursor, ':v1', vCurrval);
	 vDummy := DBMS_SQL.EXECUTE(vCursor);
     DBMS_SQL.CLOSE_CURSOR(vCursor);
	 p_NextVal := vCurrVal;
	 --commit;
   else
     DBMS_SQL.CLOSE_CURSOR(vCursor);
	 p_NextVal := vCurrVal;
     --rollback;
   end if;
   RETURN vCurrval;

now if you didn't want to use @@Identity you could do this in sql with the following

CREATE PROCEDURE GetNextID
@TableName VARCHAR(100),
@NextValue INT OUTPUT
AS
  BEGIN TRAN
    UPDATE Sequences 
       SET currval = currval + 1
     WHERE [Name] = @TableName 
    SELECT @NextValue = currval 
      FROM Sequences
     WHERE [Name] = @TableName 
   COMMIT TRAN

To boot in SQL server this procedure is probably already compiled where in oracle it needs to be Parsed, Resolved, Optimised and Compiled.

You tell me which one you'd rather work with?

 

[SemperFiDownUnda]

Oh the COMMIT and ROLLBACK where commented out because for a bit we had this being called from a component under DTC and since Oracle 8 does not support mutliple levels of transaction when in a distributed transation it would finish but give an error to the caller.

I'm looking through my archives, some where I have some stored procs that extends sp_depends. Maybe terry has something on hand.