Just testing some new forum capabilities... please ignore.

Question

Just testing some new forum capabilities... please ignore.

Jeff Moden

SSC Guru

Points: 1004434
More actions
March 6, 2008 at 4:57 pm

#120264

The following is a test to see if SSC-475 has been resolved...
DROP TABLE mm_CompanyNo
DROP PROCEDURE GetNextCustNo
GO
--==============================================================================================
-- Setup for the test
--==============================================================================================
--===== Create sequence table similar to Allen's
CREATE TABLE dbo.mm_CompanyNo (Last_Cust_No INT, Company VARCHAR(50))
--===== Seed the table with starting values for each company
--This will be the "0" company in this example
INSERT INTO mm_CompanyNo VALUES ( 0,'ABC') --First new id will be "000001"
--This will be the "M" company in this example
INSERT INTO mm_CompanyNo VALUES (100,'DEF') --First new id will be "M00101"
GO
--===== Create a procedure to get a new customer number depending on the company
CREATE PROCEDURE dbo.GetNextCustNo (@Company VARCHAR(50), @NewCustNo VARCHAR(6) OUTPUT)
AS
DECLARE @NextID INT
SET NOCOUNT ON
UPDATE dbo.mm_CompanyNo
SET @NextID = Last_Cust_No = Last_Cust_No+1
WHERE Company = @Company
SET @NewCustNo = CASE @Company
WHEN 'DEF'
THEN 'M'
ELSE '0'
END
+ REPLACE(STR(@NextID,5),' ','0')
GO
--==============================================================================================
-- Demo how to use this bugger
--==============================================================================================
--===== Declare local variables for the test
DECLARE @Company VARCHAR(50)
DECLARE @NewCustNo VARCHAR(6)
SET @Company = 'ABC'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
SET @Company = 'DEF'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
The following is a test to see if SSC-521 has been resolved...
[font="Courier New"]DROP TABLE mm_CompanyNo
DROP PROCEDURE GetNextCustNo
GO
--==============================================================================================
-- Setup for the test
--==============================================================================================
--===== Create sequence table similar to Allen's
CREATE TABLE dbo.mm_CompanyNo (Last_Cust_No INT, Company VARCHAR(50))
--===== Seed the table with starting values for each company
--This will be the "0" company in this example
INSERT INTO mm_CompanyNo VALUES ( 0,'ABC') --First new id will be "000001"
--This will be the "M" company in this example
INSERT INTO mm_CompanyNo VALUES (100,'DEF') --First new id will be "M00101"
GO
--===== Create a procedure to get a new customer number depending on the company
CREATE PROCEDURE dbo.GetNextCustNo (@Company VARCHAR(50), @NewCustNo VARCHAR(6) OUTPUT)
AS
DECLARE @NextID INT
SET NOCOUNT ON
UPDATE dbo.mm_CompanyNo
SET @NextID = Last_Cust_No = Last_Cust_No+1
WHERE Company = @Company
SET @NewCustNo = CASE @Company
WHEN 'DEF'
THEN 'M'
ELSE '0'
END
+ REPLACE(STR(@NextID,5),' ','0')
GO
--==============================================================================================
-- Demo how to use this bugger
--==============================================================================================
--===== Declare local variables for the test
DECLARE @Company VARCHAR(50)
DECLARE @NewCustNo VARCHAR(6)
SET @Company = 'ABC'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
SET @Company = 'DEF'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo[/font]
[font="Courier New"]DROP TABLE mm_CompanyNo
DROP PROCEDURE GetNextCustNo
GO
--==============================================================================================
-- Setup for the test
--==============================================================================================
--===== Create sequence table similar to Allen's
CREATE TABLE dbo.mm_CompanyNo (Last_Cust_No INT, Company VARCHAR(50))
--===== Seed the table with starting values for each company
--This will be the "0" company in this example
INSERT INTO mm_CompanyNo VALUES ( 0,'ABC') --First new id will be "000001"
--This will be the "M" company in this example
INSERT INTO mm_CompanyNo VALUES (100,'DEF') --First new id will be "M00101"
GO
--===== Create a procedure to get a new customer number depending on the company
CREATE PROCEDURE dbo.GetNextCustNo (@Company VARCHAR(50), @NewCustNo VARCHAR(6) OUTPUT)
AS
DECLARE @NextID INT
SET NOCOUNT ON
UPDATE dbo.mm_CompanyNo
SET @NextID = Last_Cust_No = Last_Cust_No+1
WHERE Company = @Company
SET @NewCustNo = CASE @Company
WHEN 'DEF'
THEN 'M'
ELSE '0'
END
+ REPLACE(STR(@NextID,5),' ','0')
GO
--==============================================================================================
-- Demo how to use this bugger
--==============================================================================================
--===== Declare local variables for the test
DECLARE @Company VARCHAR(50)
DECLARE @NewCustNo VARCHAR(6)
SET @Company = 'ABC'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
SET @Company = 'DEF'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo[/font]
--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 2 posts - 1 through 1 (of 1 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

Jeff Moden (3/6/2008)
The following is a test to see if SSC-475 has been resolved...
DROP TABLE mm_CompanyNo
DROP PROCEDURE GetNextCustNo
GO
--==============================================================================================
-- Setup for the test
--==============================================================================================
--===== Create sequence table similar to Allen's
CREATE TABLE dbo.mm_CompanyNo (Last_Cust_No INT, Company VARCHAR(50))
--===== Seed the table with starting values for each company
--This will be the "0" company in this example
INSERT INTO mm_CompanyNo VALUES ( 0,'ABC') --First new id will be "000001"
--This will be the "M" company in this example
INSERT INTO mm_CompanyNo VALUES (100,'DEF') --First new id will be "M00101"
GO
--===== Create a procedure to get a new customer number depending on the company
CREATE PROCEDURE dbo.GetNextCustNo (@Company VARCHAR(50), @NewCustNo VARCHAR(6) OUTPUT)
AS
DECLARE @NextID INT
SET NOCOUNT ON
UPDATE dbo.mm_CompanyNo
SET @NextID = Last_Cust_No = Last_Cust_No+1
WHERE Company = @Company
SET @NewCustNo = CASE @Company
WHEN 'DEF'
THEN 'M'
ELSE '0'
END
+ REPLACE(STR(@NextID,5),' ','0')
GO
--==============================================================================================
-- Demo how to use this bugger
--==============================================================================================
--===== Declare local variables for the test
DECLARE @Company VARCHAR(50)
DECLARE @NewCustNo VARCHAR(6)
SET @Company = 'ABC'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
SET @Company = 'DEF'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
The following is a test to see if SSC-521 has been resolved...
[font="Courier New"]DROP TABLE mm_CompanyNo
DROP PROCEDURE GetNextCustNo
GO
--==============================================================================================
-- Setup for the test
--==============================================================================================
--===== Create sequence table similar to Allen's
CREATE TABLE dbo.mm_CompanyNo (Last_Cust_No INT, Company VARCHAR(50))
--===== Seed the table with starting values for each company
--This will be the "0" company in this example
INSERT INTO mm_CompanyNo VALUES ( 0,'ABC') --First new id will be "000001"
--This will be the "M" company in this example
INSERT INTO mm_CompanyNo VALUES (100,'DEF') --First new id will be "M00101"
GO
--===== Create a procedure to get a new customer number depending on the company
CREATE PROCEDURE dbo.GetNextCustNo (@Company VARCHAR(50), @NewCustNo VARCHAR(6) OUTPUT)
AS
DECLARE @NextID INT
SET NOCOUNT ON
UPDATE dbo.mm_CompanyNo
SET @NextID = Last_Cust_No = Last_Cust_No+1
WHERE Company = @Company
SET @NewCustNo = CASE @Company
WHEN 'DEF'
THEN 'M'
ELSE '0'
END
+ REPLACE(STR(@NextID,5),' ','0')
GO
--==============================================================================================
-- Demo how to use this bugger
--==============================================================================================
--===== Declare local variables for the test
DECLARE @Company VARCHAR(50)
DECLARE @NewCustNo VARCHAR(6)
SET @Company = 'ABC'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
SET @Company = 'DEF'
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo
EXEC dbo.GetNextCustNo @Company,@NewCustNo OUT
PRINT @NewCustNo[/font]

Quote test to see if formatting is retained for both SSC's.

--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)