Auto generate sp's for any table * usp_create_sps

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


    -Ken

  • 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.

    Change is inevitable... Change for the better is not.


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

  • 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.

    Change is inevitable... Change for the better is not.


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

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

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