Promotional codes generator in T-SQL
Recently asked to generate codes for an on pack promotion. As I frequently forget how to do these type of SQL things. I thought I would record this.
Firstly, create a table to hold you codes and set an index on the code column, also make it a primary key – so there is no way we can have multiple entries with the same value.
CREATE TABLE [dbo].[Codes](
[nvarchar](10) NOT NULL,
CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Once done, create and run the following stored procedure – this is for 100000 codes of 10 alpha-numeric characters, just adjust as required.
CREATE PROCEDURE [dbo].[uspCodeGenerate]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ui uniqueidentifier
DECLARE @longcode nvarchar(36)
DECLARE @code nvarchar(10)
-- DELETE FROM [AppData].[dbo].[Code] --if you want to clear the table
WHILE (SELECT count (*) FROM dbo.Codes WITH (INDEX(IX_Code)) ) < 100000
BEGIN
SET @ui = newid()
SET @longcode = (SELECT CAST(@ui AS NVARCHAR(36)))
SET @longcode = (SELECT REPLACE(@longcode,'-',''))
SET @code = (SELECT SUBSTRING(@longcode,1,10))
-- replace the vowels, as these are often excluded, where '?' are characters of your choice!
SET @code = (SELECT REPLACE(@code,'A','?'))
SET @code = (SELECT REPLACE(@code,'E','?'))
SET @code = (SELECT REPLACE(@code,'I','?'))
SET @code = (SELECT REPLACE(@code,'O','?'))
SET @code = (SELECT REPLACE(@code,'U','?'))
IF NOT EXISTS (SELECT * FROM dbo.Codes WITH (INDEX(IX_Code)) WHERE code = @code)
BEGIN
INSERT INTO dbo.Codes (code) VALUES (@code)
END
END
END
By default, theses are displayed in alpha numeric order, so to mix this up a bit we can do this:
SELECT * FROM [AppData].[dbo].[Code] order by newid()
Advertisement
