Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auto generate sp's for any table * usp_create_sps Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2007 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:54 AM
Points: 194, Visits: 322
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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #402778
Posted Tuesday, September 25, 2007 5:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #402779
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse