Sql server-t-sql: how do I declare table datatype and fetch a cursor into it?

Posted on 2006-03-24
Last Modified: 2012-06-27
Hi experts,
I am writing a stored procedure in sql server and instead of declaring so many local variable to hold the resultset from a cursor, I want to be able to declare a rowtype or tabletype datatype to hold the resultset. I am new to sql server but have worked with Oracle database.
Can someone please help alongwith code examples? Thanks
Question by:uzairp
    LVL 14

    Accepted Solution

    This question comes usually from Oracle to SQL Server newcomers. :-)

    Unfortunately, There's no way to declare a %ROWTYPE variable in SQL Server. You need to declare as many variables as needed to hold values from a record.


      Nayer Naguib
    LVL 14

    Expert Comment

    Here's what "Microsoft SQL Server Migration Assistant for Oracle" says:

    • Variable declarations including %ROWTYPE on Oracle will be converted to a group of local variables on SQL Server.


      Nayer Naguib

    Author Comment

    Now I know I dont have to spend any more time looking for an equivalent of rowtype. Thanks

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    ShiftLeft challenge 21 50
    linearIn  challenge 23 54
    wordsWithoutList  challenge 24 61
    countAbc challenge 9 35
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    This article will show, step by step, how to integrate R code into a R Sweave document
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now