September 25, 2007 at 12:53 pm
Comments posted to this topic are about the item Auto generate sp's for any table * usp_create_sps
-Ken
September 25, 2007 at 5:27 pm
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
Change is inevitable... Change for the better is not.
September 25, 2007 at 5:41 pm
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply