Cursor Conversion

• runelords

  Ten Centuries

  Points: 1275

  More actions

  February 28, 2005 at 3:51 pm

  #163742

  Is there a way to Use a Cursor with parameters in TSQL?

  Example:

  DECLARE c_get_contact_id(p_employer_id int) CURSOR LOCAL FOR

  SELECT contact_id

  FROM EMPLOYER_CONTACTS

  WHERE employer_id = p_employer_id

  This is based on an Oracle PL/SQL statement:

  CURSOR c_get_contact_id (p_employer_id IN NUMBER)

  IS

  SELECT contact_id

  FROM EMPLOYER_CONTACTS

  WHERE employer_id = p_employer_id;

  
  Arthur Lorenzini

• philcart

  SSC-Forever

  Points: 47797

  More actions

  February 28, 2005 at 7:19 pm

  #544524

  If p_employer_id int is supposed to be your parameter, then yes it's possible although you dont have the syntax right. It should be something like,

  DECLARE c_get_contact_id CURSOR LOCAL FOR 
      SELECT contact_id
      FROM EMPLOYER_CONTACTS
      WHERE employer_id = @p_employer_id

  Of course you will need to declare the @p_employer_id prior to declaring the cursor.

  On another tack, why do you need to use a cursor? Could what you're trying to achieve be solved with a set based solution?

   

  --------------------
  Colt 45 - the original point and click interface