Auto generate sp's for any table * usp_create_sps

  • spongemagnet

    SSCrazy

    Points: 2812

    Comments posted to this topic are about the item Auto generate sp's for any table * usp_create_sps


    -Ken

  • Jeff Moden

    SSC Guru

    Points: 994647

    I'm thinking there's a couple of bugs... I have a table that looks like this...

    --===== Create and populate a 1,000,000 row test table.

    -- Column RowNum has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

    -- Takes about 77 seconds to execute.

    SELECT TOP 1000000

    RowNum = IDENTITY(INT,1,1),

    SomeInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),

    SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))

    + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),

    SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

    SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),

    SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)

    INTO dbo.JBMTest

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN

    --===== A table is not properly formed unless a Primary Key has been assigned

    ALTER TABLE dbo.JBMTest

    ADD PRIMARY KEY CLUSTERED (RowNum)

    ... and when I run your proc on it, it get this including the following errors...

    Server: Msg 536, Level 16, State 3, Procedure usp_create_sps, Line 245Invalid length parameter passed to the substring function.

    create procedure usp_insert_bigtest (

    @RowNum int

    ,@SomeID int

    ,@SomeString char(2)

    ,@SomeNumber money

    ,@SomeDate datetime

    ,@LastModifiedBy varchar(50)

    ,@LastModifiedOn datetime

    ) as

    insert

    bigtest (

    RowNum,

    SomeID,

    SomeString,

    SomeNumber,

    SomeDate,

    LastModifiedBy,

    LastModifiedOn

    )

    values (

    @RowNum,

    @SomeID,

    @SomeString,

    @SomeNumber,

    @SomeDate,

    @LastModifiedBy,

    @LastModifiedOn

    )

    Server: Msg 170, Level 15, State 1, Procedure usp_update_bigtest, Line 19

    Line 19: Incorrect syntax near 'where'.

    create procedure usp_update_bigtest (

    @RowNum int

    ,@SomeID int

    ,@SomeString char(2)

    ,@SomeNumber money

    ,@SomeDate datetime

    ,@LastModifiedBy varchar(50)

    ,@LastModifiedOn datetime

    ) as

    update

    bigtest

    set RowNum=@RowNum,

    SomeID=@SomeID,

    SomeString=@SomeString,

    SomeNumber=@SomeNumber,

    SomeDate=@SomeDate,

    LastModifiedBy=@LastModifiedBy,

    LastModifiedOn=@LastModifiedOn

    where

    Server: Msg 170, Level 15, State 1, Procedure usp_delete_bigtest, Line 12

    Line 12: Incorrect syntax near 'where'.

    create procedure usp_delete_bigtest (

    @RowNum int

    ,@SomeID int

    ,@SomeString char(2)

    ,@SomeNumber money

    ,@SomeDate datetime

    ,@LastModifiedBy varchar(50)

    ,@LastModifiedOn datetime

    ) as

    delete

    bigtest

    where

    Server: Msg 170, Level 15, State 1, Procedure usp_select_bigtest, Line 19

    Line 19: Incorrect syntax near 'where'.

    create procedure usp_select_bigtest (

    @RowNum int

    ,@SomeID int

    ,@SomeString char(2)

    ,@SomeNumber money

    ,@SomeDate datetime

    ,@LastModifiedBy varchar(50)

    ,@LastModifiedOn datetime

    ) as

    select

    RowNum,

    SomeID,

    SomeString,

    SomeNumber,

    SomeDate,

    LastModifiedBy,

    LastModifiedOn

    from bigtest

    where

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994647

    And, sorry about the triple spaced code... the IFCODE's used to work correctly and they don't seem to, anymore... can't even color the damned errors anymore... they really messed something up...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply