Category: SQL Server

Generate Fixed Digit OTP Code in sql server

Leave a comment

RAND() function simply generate a decimal number between 0 (inclusive) and 1 (exclusive). The logic is to multiply value returned by RAND() by 1 billion so that it has enough digits and apply LEFT function to extract 6 digits needed.

1SELECTLEFT(CAST(RAND()*1000000000 ASINT),6) asOTP

Now as RAND() sometimes give 0, to handle it, we can change the code to

1SELECTLEFT(CAST(RAND()*1000000000+999999 ASINT),6) asOTP

so that you always get 6 digit number.

SQL SERVER - Simple Method to Generate Fixed Digit OTP Code Using Random Function otp

This is really a simple method of generating 6 digit number. It can be easily extended to any number of digits.

Source: https://blog.sqlauthority.com/2018/08/22/sql-server-simple-method-to-generate-fixed-digit-otp-code-using-random-function/

Generate random id in sql server

Leave a comment 
create proc GenerateRandomID
    @size       int
as
begin

    declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    declare @i int = 0
    declare @id varchar(max) = ''

    while @i < @size
    begin
        if rand() > .5
            set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)
        else
            set @id = @id + cast(floor(rand() * 10) as varchar(2))
        set @i = @i + 1
    end

    select @id

end
go



exec GenerateRandomID 15

-------------------
BWZBKR601I8Z9KV

(1 row(s) affected)

Source: https://stackoverflow.com/questions/13959385/generate-random-id-fixed-length-in-t-sql

Query that returns list of all Stored Procedures in an MS SQL database

Leave a comment

 

How to Get List of all Stored Procedures in an MS SQL database.

Solution 1

select *  from YourDatabaseName.information_schema.routines 
 where routine_type = 'PROCEDURE'

Solution 2

select *   from YourDatabaseName.information_schema.routines 
 where routine_type = 'PROCEDURE' 
   and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

Note: retrun Prodecdure Name Not Start from ‘sp_’, ‘xp_’, ‘ms_’

Solution 3

SELECT name, type   FROM dbo.sysobjects
 WHERE (type = 'P')

 

Microsoft introduced a whole swathe of system catalogue views that can be used for this kind of query.
To get a list of stored procedures, one would simply select from the sys.procedures catalogue view
ie.

select name
from sys.procedures
order by name

Other useful catalogue views are

sys.tables<br />
sys.indexes<br />
sys.indexcolumns<br />
sys.foreign_keys<br />
sys.types<br />

 

Source: https://www.codeproject.com/Tips/865795/Query-that-returns-list-of-all-Stored-Procedures-i

 

How to use LIKE in a t-sql dynamic statement in a stored procedure?

Leave a comment

The % characters have to be in the search string…

SET @search = '%' + @search + '%'
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE @search'

Note that the following would also work, but introduces potential for a SQL injection vulnerability…

-- DON'T do this!
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE ''%' + @search + '%'''

 

Source: https://stackoverflow.com/questions/5383634/how-to-use-like-in-a-t-sql-dynamic-statement-in-a-stored-procedure

 

Difference between Identity and Sequence in SQL Server

Leave a comment
S.No Identity Sequence
1 Dependant on table. Independent from table.
2 Identity is a property in a table.

Example :

CREATE TABLE Table
test_Identity

(

[ID] int Identity (1,1),

[Product Name] varchar(50)

)

 

 

 Sequence is an object.

Example :

CREATE SEQUENCE [dbo].[Sequence_ID]

AS [int]

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 1000

NO CYCLE

NO CACHE
3 If you need a new ID from an identity column you need to
insert and then get new ID.

Example :

Insert into [test_Identity] Values (‘SQL Server’)

GO

SELECT @@IDENTITY AS ‘Identity’

–OR

Select SCOPE_IDENTITY() AS‘Identity’

 In the sequence, you do not need to insert new ID, you can view the new ID directly.

Example :

SELECT NEXT VALUE
FOR dbo.[Sequence_ID]

 

 
4 You cannot perform a cycle in identity column. Meaning, you cannot restart the counter after a
particular interval.

 

 In the sequence, you can simply add one property to make it a cycle.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CYCLE;
5 You cannot cache Identity column property. Sequence can be easily cached by just setting cache property of
sequence. It also improves the performance.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CACHE 3;
6 You cannot remove the identity column from the table directly. The sequence is not table dependent so you can easily remove it

Example :

Create table dbo.[test_Sequence]

(

[ID] int,

[Product Name] varchar(50)

)

GO

–First Insert With Sequence object

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (NEXT VALUEFOR [Ticket] , ‘MICROSOFT SQL SERVER 2008’)

GO

–Second Insert without Sequence

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (2 ,‘MICROSOFT SQL SERVER 2012’)
7 You cannot define the maximum value in identity column it is
based on the data type limit.		 Here you can set up its maximum value.

 

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

MAXVALUE 2000;
8 You can reseed it but cannot change the step size.

Example :

DBCC CHECKIDENT (test_Identity,RESEED, 4)

 

 You can reseed as well as change the step size.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

RESTART WITH 7

INCREMENT BY 2;
9 You cannot generate range from identity. You can generate a range of sequence
values from a sequence object with the help of sp_sequence_get_range.

 

Source: https://raresql.com/2012/05/01/difference-between-identity-and-sequence/