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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy