Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Auto generate sp's for any table * usp_create_sps Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, September 25, 2007 12:53 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, October 28, 2013 10:54 AM Points: 182, Visits: 285
 Comments posted to this topic are about the item Auto generate sp's for any table * usp_create_sps -Ken
Post #402713
 Posted Tuesday, September 25, 2007 5:27 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 9:51 PM Points: 34,581, Visits: 28,764
 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #402778
 Posted Tuesday, September 25, 2007 5:41 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 9:51 PM Points: 34,581, Visits: 28,764
 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #402779

 Permissions