Professional Web Applications Themes

Abstraction of table variable declaration - Microsoft SQL / MS SQL Server

Hi all, I'm trying to simplify the maintenance of my stored procedures using SQL Server 2000's table variables. Currently each sp returns multiple result sets (1 to 4) that were using a large SELECT statement (with multiple table joins) to query the same subset of data in different ways. I've managed to abstract the creation of the data subset by using a table valued udf and I intend to use this in the sp's to simplify the SELECT statements. My problem is I can't work out how to abstract the creating of the table variable itself. Ideally I'd like to ...

  1. #1

    Default Abstraction of table variable declaration

    Hi all,

    I'm trying to simplify the maintenance of my stored procedures using SQL
    Server 2000's table variables.

    Currently each sp returns multiple result sets (1 to 4) that were using
    a large SELECT statement (with multiple table joins) to query the same
    subset of data in different ways.

    I've managed to abstract the creation of the data subset by using a
    table valued udf and I intend to use this in the sp's to simplify the
    SELECT statements. My problem is I can't work out how to abstract the
    creating of the table variable itself.

    Ideally I'd like to do something like:

    CREATE PROCEDURE dbo.usp_TheSPName AS
    DECLARE WorkingTable TABLE (dbo.udf_CreateTable())
    INSERT INTO WorkingTable SELECT * FROM dbo.FillTable()

    -- run SELECTS here...

    This would mean I could maintain the structure and contents of the data
    subset in two places rather than 500+!

    Any ideas (I'm not dead set on using udf's either..)?
    Thanks
    SiWhite

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    SiWhite Guest

  2. #2

    Default Re: Abstraction of table variable declaration

    Thanks for the advice Jacco.
    My profiling would have picked up the performance hit but you've saved
    me some pointless work.



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    SiWhite Guest

Similar Threads

  1. Assigning a table value from a dataset to a variable
    By blitzed2010 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 29th, 07:37 PM
  2. Insert ASP variable into SQL table
    By Mintyman in forum Dreamweaver AppDev
    Replies: 4
    Last Post: April 1st, 09:39 AM
  3. One line variable declaration with multiple conditions
    By Dan Muey in forum PERL Beginners
    Replies: 15
    Last Post: January 8th, 07:22 PM
  4. Global Variable declaration
    By Yarden Sheffer in forum Macromedia Director Lingo
    Replies: 5
    Last Post: July 23rd, 08:44 AM
  5. Usage and Benefit of Table Variable
    By CDARS in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 08:04 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139