SQL

Variable

 

DECLARE @Category int;

DECLARE @Variable1 DataType1, @Variable2
DataType2, @Variable_n DataType_n;

    DECLARE @Number tinyint,

            @Result tinyint;

 

Unlike many other languages like C/C++, C#, Java, or Pascal, if you
declare many variables that use the same data type, the name of each variable
must be followed by its own data type.

Initializing a Variable

 

SELECT @VariableName
= DesiredValue

or

SET @VariableName =
DesiredValue

    SET @Result = @Number + 20;

 

Here is an example

DECLARE @Category int

SET @Category = 1450

PRINT @Category

GO

 

/////////////////////////////////////////////////////////////////////////////////////////////////////

DECLARE @DateHired As datetime2, @CurrentDate As datetime2

SET @DateHired = N’1996/10/04′

SET @CurrentDate = N’2007/04/11′

IF @DateHired < @CurrentDate

PRINT N’You have the
experience required for a new promotion in this job’
GO

 

SET @minSubTotal =
9>     (SELECT TOP BillingTotal
10>         FROM Billings ORDER BY BillingTotal)

 

Declare     @LastName varchar(50),

            @FirstName
varchar(20),

            @BirthDate
smalldatetime

DECLARE @TotalCost money

 

    SET @TotalCost =
10

    SET @TotalCost =
@TotalCost * 1.1

    DECLARE @Test money

    select @Test =
MAX(UnitPrice) FROM OrderDetails

    SELECT @Test

 

//

SELECT @MyResult1
= SQRT(@MyNumber1), @MyResult2 = SQRT(@MyNumber2)

 

IF Statement

 

DECLARE @a int,
@t int ;

SET @a = 1450;

select @t=45;

–select @a+@t
as [Total];

 

if @a < @t

begin

      Print @a;

            Print 1+1;

            end 

else

begin

      print
@t;  

      Print 1+2;

 

      end

GO

 

CASE Statement

CASE…WHEN…THEN…ELSE

CASE Expression

        WHEN Value1
THEN Result

        WHEN Value2
THEN Result

        WHEN Value_n THEN Result

       

        ELSE Alternative

END

The ELSE statement, as the last, is used when none of the values of the
WHEN statements fits. Here is an example:

DECLARE @CharGender Char(1),

        @Gender  Varchar(20);

SET @CharGender = ‘g’;

SET @Gender =

        CASE @CharGender

                WHEN
N’m‘ THEN N’Male

                WHEN
N’M’ THEN N’Male

                WHEN
N’f‘ THEN N’Female

                WHEN
N’F’ THEN N’Female

                ELSE ‘Unknown’

                     END;

 

SELECT
N’Student Gender: ‘ + @Gender;

GO

This would produce:

If you don’t produce an ELSE statement but a value not addressed
by any of the WHEN statements is produced, the result would be NULL.
Here is an example:

This means that it is a
valuable safeguard to always include an ELSE sub-statement in a CASE
statement.

Else is optional.

 

IF (SELECT COUNT(*) FROM inserted)

SELECT CASE

WHEN 1 < 1 THEN ‘TRUE’

ELSE ‘FALSE’ END AS Result;

 

SELECT IIF(2 > 1, ‘yes’,’no‘)

 

Then your
statement will be:

 

SELECT isnull((select
‘yes’ where 2 > 1),’no’)

 

///

IF (0 <> 0)
AND (1/0 > 0)

IF (0 <> 0)
OR (1/0 > 0)

 

LOOP Statements

DECLARE @Number As int

SET @Number = 1

WHILE @Number < 5

    BEGIN

                     SELECT
@Number AS Number

                     SET
@Number = @Number + 1

    END

GO

 

Try Catch Statement

BEGIN TRY

    DECLARE @Number tinyint,

            @Result tinyint;

               

    SET @Number = 252;

    SET @Result = @Number + 20;

               

    SELECT @Number AS Number, @Result AS
Result;

END TRY

BEGIN CATCH

    PRINT N’Error
Line: ‘ + CAST(ERROR_LINE() AS nvarchar(100));

   

END CATCH

 

ERROR

ERROR_NUMBER()

PRINT N’Error #: ‘ + CAST(@@ERROR AS
NVARCHAR(50
)); //same

 

BEGIN TRY

    DECLARE @Number tinyint,

                    @Result tinyint;

               

    SET @Number = 252;

    SET @Result = @Number + 20;

               

    SELECT @Number AS Number, @Result AS
Result;

END TRY

BEGIN CATCH

    IF
@@ERROR = 220

        PRINT N’Something
went wrong with your code’;

END CATCH

 

PRINT ERROR_MESSAGE();

Managing Datetime

DECLARE @SomeDate Date;

SET         @SomeDate =
N’5-7-05′;

PRINT    @SomeDate;

 

 

DATE DATEFROMPARTS(int year, int month, int day)

 

To let you get the
current date (and time) of the computer that a user is using, Transact-SQL
provides a function named GETDATE. Its syntax is:

 

GETDATE();

To get a date with
more precision, call the SYSDATETIME function. Its syntax is:

 

SYSDATETIME();

TRY_CONVERT(date, N’1995-01-01′, 102)

////

DECLARE @DateHired As date,

        @CurrentDate As date;

SET @DateHired = N’2005/10/04′;

SET @CurrentDate  = N’20130622′;

SELECT DATEDIFF(Year, @DateHired, @CurrentDate)

       AS
[Current Experience];

GO

/////////

DECLARE @Original date,

      @Result date;

SET     @Original =
N’20121204′;

SET     @Result = DATEADD(Quarter, 2, @Original);

SELECT @Original
[Original Date];

SELECT @Result [2
Years Later];

GO

///////

 

TypeOfValue Description

Year  yy    yyyy  A number of years
will be added to the date value

quarter     q     qq    A number of
quarters of a year will be added to the date value

Month m     mm    A number of months
will be added to the date value

dayofyear   y     dy    A number of days of a year will be added to
the date value

Day   d     dd    A number of days will be added to the date value

Week  wk    ww    A number of weeks will be added to the date value

 ////////////////

DATENAME()

DATEPART()

DAY()

//

DECLARE @DateValue DATE,

        @Result nvarchar(30);

SET @DateValue = N’20121004′;

SET @Result = DATENAME(mm, @DateValue);

SELECT @DateValue AS Original;

SELECT @Result AS Result;

GO

//

DATENAME=
can b used 4 day, month, quarter etc

///

YEAR()

//////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE PROCEDURE spInsertOrUpdateEmployee

 @FirstName nVarChar(50),

 @LastName nVarChar(25),

@Salary Money

AS

IF EXISTS(SELECT * From Employee Where First_name
= @FirstName)

UPDATE Employee SET
Last_NAME = @LastName,
Salary = @Salary

 WHERE First_name = @FirstName

 ELSE

 INSERT INTO Employee (ID,First_Name, Last_Name,
Salary)

 SELECT 99, @FirstName,
@LastName, @Salary

 GO

///Optional param

CREATE PROC
spInvTotal2

        @DateVar smalldatetime = NULL

 AS

 IF @DateVar IS NULL

    SELECT @DateVar =
MIN(BillingDate) FROM
Billings

 SELECT SUM(BillingTotal)

FROM Billings

 WHERE BillingDate
>= @DateVar

 GO

////

SELECT @InvTotal = SUM(BillingTotal)

 FROM Billings JOIN Bankers

     ON Billings.BankerID
= Bankers.BankerID

 WHERE (BillingDate
>= @DateVar) AND

       (BankerName
LIKE @BankerVar)

///OP param

CREATE PROC
spInvTotal1

        @DateVar smalldatetime,

        @InvTotal
money OUTPUT

 AS

SELECT @InvTotal = SUM(BillingTotal)

FROM Billings

WHERE BillingDate >= @DateVar

GO

 

/////////////Order
by

Create PROC
GetAuthors1

   @colnum AS int

 AS

 SELECT  
* FROM   authors ORDER BY

   CASE @colnum

     WHEN 1 THEN au_id

     WHEN 2 THEN au_lname

     WHEN 3 THEN au_fname

     WHEN 4 THEN phone

     WHEN 5 THEN address

     WHEN 6 THEN city

     WHEN 7 THEN state

     WHEN 8 THEN zip

     WHEN 9 THEN CAST(contract
AS CHAR(1))

     ELSE NULL

   END

 GO

Dynamic SQL

 

CREATE PROC GetAuthors2

   @colnum AS int

 AS

 DECLARE

   @cmd AS varchar (8000)

SET @cmd =

   ‘SELECT *’     + CHAR (13) + CHAR(10) +

   ‘FROM authors’
+ CHAR (13) + CHAR(10) +

   ‘ORDER BY ‘    + CAST (@colnum AS varchar (4))

  EXEC(@cmd)

GO

//////////////////////////////////////////////////////////////////////////////////////////////////////

///ISNULL

DECLARE   @intTest int

 SELECT   
ISNULL(@intTest,5)

SELECT    @intTest = 37

SELECT    ISNULL(@intTest,5)

////Var as Field

 DECLARE @chvField1 VARCHAR(30),

             @chvField2 VARCHAR(30),

            @chvSQL VARCHAR(255)

    SELECT @chvField1 = ‘au_fname

    SELECT @chvField2 = ‘au_lname

    SELECT @chvSQL =
‘SELECT ‘ + @chvField1 + ‘, ‘ + @chvField2 + ‘ FROM authors’

    EXEC (@chvSQL)

//////////////////////////////////////////////////////////////////////////////////////////////////////

//

CREATE PROCEDURE spInsertOrUpdateEmployee

4> @FirstName nVarChar(50),

5> @LastName nVarChar(25),

6> @Salary
Money

7> AS

8> IF EXISTS(SELECT * From Employee Where First_name
= @FirstName)

9> UPDATE
Employee SET Last_NAME = @LastName,
Salary = @Salary

10> WHERE First_name = @FirstName

11> ELSE

12> INSERT
INTO Employee (ID,First_Name,
Last_Name, Salary)

13> SELECT 99,
@FirstName, @LastName,
@Salary

 

///////

 WHILE (SELECT SUM(BillingTotalCreditTotalPaymentTotal) FROM Billings) >= 50000

4>     BEGIN

5>         UPDATE Billings

6>         SET CreditTotal
= CreditTotal + .01

7>         WHERE BillingTotal  CreditTotal
PaymentTotal > 0

8>         IF (SELECT MAX(CreditTotal) FROM Billings) > 3000

9>             BREAK

10>         ELSE –(SELECT
MAX(CreditTotal) FROM Billings) <= 3000

11>             CONTINUE

12>     END

 

//////

DECLARE @Counter Int

8> SET
@Counter = 1

9> WHILE
@Counter < 4

10>  BEGIN

11>   PRINT SubCategory

12>   SELECT Name, ProductID

13>   FROM Product

14>   WHERE ProductID =
@Counter

15>    SET @Counter = @Counter + 1

16>  END

////

 WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)

21>    BEGIN

22>          SELECT @Counter = @Counter + 1

23>          FETCH NEXT FROM CursorTest
INTO @OrderID, @CustomerID

24>          PRINT ‘Row ‘ + CONVERT(varchar,@Counter) + ‘ has an OrderID
of ‘ +

25>          CONVERT(varchar,@OrderID) + ‘ and a CustomerID
of ‘ + @CustomerID

26>    END

////

BREAK

12>    ELSE

13>     

14>      CONTINUE

TRANSACTION

 

CREATE PROC test
as

3> BEGIN
TRANSACTION

4> INSERT c
VALUES (‘X’)

5>     IF (@@ERROR <> 0) GOTO on_error

6> INSERT b
VALUES (‘X’) 
Fails reference

7>     IF (@@ERROR <> 0) GOTO on_error

8> COMMIT
TRANSACTION

9> RETURN(0)

on_error:

12> ROLLBACK
TRANSACTION

13> RETURN(1)

 

GOTO label

label
definition: code

////

3> IF EXISTS
(SELECT * FROM sysobjects WHERE name=’show_error

4>     AND type=’U’)

5>     DROP TABLE show_error

6> GO

1>

2> CREATE TABLE
show_error

3> (

4> col1    smallint NOT NULL
PRIMARY KEY,

5> col2    smallint NOT NULL

6> )

7> GO

1>

2> BEGIN
TRANSACTION

3> INSERT show_error VALUES (1, 1)

4> IF @@ERROR
<> 0 GOTO TRAN_ABORT

5> INSERT show_error VALUES (1, 2)

6> if @@ERROR
<> 0 GOTO TRAN_ABORT

7> INSERT show_error VALUES (2, 2)

8> if @@ERROR
<> 0 GOTO TRAN_ABORT

9> COMMIT
TRANSACTION

10> GOTO
FINISH

11>

12>
TRAN_ABORT:

13> ROLLBACK
TRANSACTION

14>

15> FINISH:

16> GO

////

BEGIN TRAN

3> DELETE
Billings

4> WHERE BankerID = 34

5> IF @@ROWCOUNT
> 1

6>     BEGIN

7>         ROLLBACK TRAN

8>         PRINT ‘More Billings than expected.
Deletions rolled back.’

9>     END

10> ELSE

11>     BEGIN

12>         DELETE Bankers

13>         WHERE BankerID
= 34

14>         COMMIT TRAN

15>         PRINT ‘Deletions committed to the
database.’

16>     END

17> GO

////

SELECT BankerID, BankerName

5> INTO #BankerCopy

6> FROM
Bankers

7> WHERE BankerID < 5

8> BEGIN TRAN

9>   DELETE #BankerCopy
WHERE BankerID = 1

10>   SAVE TRAN Banker1

11>     DELETE #BankerCopy
WHERE BankerID = 2

12>     SAVE TRAN Banker2

13>       DELETE #BankerCopy
WHERE BankerID = 3

14>       SELECT * FROM #BankerCopy

15>     ROLLBACK TRAN Banker2

16>     SELECT * FROM #BankerCopy

17>   ROLLBACK TRAN Banker1

18>   SELECT * FROM #BankerCopy

19> COMMIT
TRAN

////

BEGIN TRANSACTION

7> GO

1> SELECT 1/0
AS DivideByZero

2> GO

Msg
8134, Level 16, State 1, Server J\SQLEXPRESS, Line 1

Divide by zero
error encountered.

1> SELECT
@@TRANCOUNT AS ActiveTransactionCount

2> GO

////

SELECT
@@TRANCOUNT — After ROLLBACK, always Returns 0!

////Table lock

BEGIN TRAN 

SELECT    *

FROM      authors (tablockx)   

WHERE     au_lname =
‘Green’   

WAITFOR DELAY
’00:02:00′   

ROLLBACK TRAN

////

 BEGIN TRAN

4> UPDATE
authors

5> SET state =
‘FL’

6> WHERE state
= ‘KS’

7>

8> IF @@ERROR
<> 0 BEGIN

9>     ROLLBACK TRAN

10>     GOTO ON_ERROR

11>  END

12> UPDATE
jobs

13> SET  min_lvl = min_lvl – 10

14> IF @@ERROR
<> 0 BEGIN

15>     ROLLBACK TRAN

16>     GOTO ON_ERROR

17>  END

18>

19> COMMIT
TRAN

20> ON_ERROR:

21>

 

////

DECLARE @BillingID int

5> BEGIN TRAN

6> INSERT INTO
Billings VALUES (0, 0, ‘2005-10-18′, 165, 1,’2005-04-22’,123,321);

7> IF @@ERROR
= 0

8>   BEGIN

9>     SET @BillingID =
@@IDENTITY

10>             INSERT INTO Billings VALUES (0, 0,
‘2005-10-18′, 165, 1,’2005-04-22’,123,321);

11>     IF @@ERROR = 0

12>       BEGIN

13>         INSERT INTO Billings VALUES (0, 0,
‘2005-10-18′, 165, 1,’2005-04-22’,123,321);

14>         IF @@ERROR = 0

15>           COMMIT TRAN

16>         ELSE

17>           ROLLBACK TRAN

18>       END

19>     ELSE

20>       ROLLBACK TRAN

21>   END

22> ELSE

23>   ROLLBACK TRAN

////

BEGIN
TRANSACTION                  Sets the
starting point of an explicit transaction.

ROLLBACK
TRANSACTION               Restores
original data modified by a transaction, and brings data back to the state it
was in at the start of the transaction. Resources held by the transaction are
freed.

COMMIT
TRANSACTION                 Ends the
transaction if no errors were encountered and makes changes permanent.
Resources held by the transaction are freed.

BEGIN DISTRIBUTED
TRANSACTION      Allows you to define the
beginning of a distributed transaction to be managed by Microsoft Distributed
Transaction Coordinator (MS DTC). MS DTC must be running locally and remotely.

SAVE TRANSACTION                   SAVE TRANSACTION issues a savepoint within a transaction, which allows one to define
a location to which a transaction can return if part of the transaction is
cancelled. A transaction must be rolled back or committed immediately after
rolling back to a savepoint.

@@TRANCOUNT                        Returns the number of
active transactions for the connection. BEGIN TRANSACTION increments
@@TRANCOUNT by 1, and ROLLBACK TRANSACTION and COMMIT
TRANSACTION decrements @@TRANCOUNT by 1. ROLLBACK TRANSACTION to a savepoint has no impact.

////

Atomicity means
that the transactions are an all-or-nothing entity carrying out all steps or
none at all

Consistency
ensures that the data is valid both before and after the transaction. Data
integrity must be maintained (foreign key references, for example) and internal
data structures need to be in a valid state.

Isolation is a
requirement that transactions not be dependent on other transactions that may
be taking place concurrently (either at the same time or overlapping). One
transaction can’t see another transaction’s data that is in an intermediate
state, but instead sees the data as it was either before the transaction began
or after.

Durability means
that the transaction’s effects are permanent after the transaction has
committed, and any changes will survive system failures.

 

 

////

SET TRANSACTION
ISOLATION LEVEL

  { READ COMMITTED

    | READ UNCOMMITTED

    | REPEATABLE READ

    | SERIALIZABLE

}

 

The READ
UNCOMMITTED option allows dirty reads.

It is the same as
using the NOLOCK or READUNCOMMITTED table hint.

It does not
create locks nor does it honor any locks.

The data you read
while it is in effect may be in the process of being changed.

 

The REPEATABLE
READ option additionally disallows phantom rows.

Shared locks
remain in place for the duration of the transaction.

The outside
process can still insert rows that will be picked up by your second SELECT.

 

The SERIALIZABLE
option is the most restrictive.

It additionally
prevents other processes from inserting rows between your first and second
SELECTs.

If the INSERT
will make no difference to your two SELECTs, it is allowed; otherwise, it is
not.

 

 

SET TRANSACTION
ISOLATION LEVEL REPEATABLE READ

4> GO

1>

2> BEGIN TRAN

3> SELECT    *

4> FROM      authors

5> WHERE     au_lname =
‘Green’

6> WAITFOR
DELAY ’00:02:00′

7> ROLLBACK
TRAN

////

SCOPE_IDENTITY() returns the last IDENTITY value inserted by your
session into any table that has an IDENTITY column, but only in the current
scope.

A scope is a
batch, a stored procedure, a trigger, or a function.

 

////

IF @Side IS NULL

      PRINT ‘A
null value is not welcome’

////

CAST
( @aa AS numeric )

////

////

////

////

////

////

 

/////////////////////////////////////////////////////////////////////////////////////////////////////

SELECT TOP 5 * FROM yourtable ORDER BY NEWID()

//////////////////////////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////////////

Copy JOIN Three Tables

SELECT p.Name, v.Name

FROM Production.Product p

JOIN Purchasing.ProductVendor
pv

ON p.ProductID = pv.ProductID

JOIN Purchasing.Vendor v

ON pv.BusinessEntityID = v.BusinessEntityID

WHERE ProductSubcategoryID
= 15

ORDER BY v.Name;

 

/////////////////////////////////////////////////////////////////////////////////////////////////////

declare @qty int

set @qty =51458989

select RIGHT (REPLICATE (‘0’, 10) + CAST (@qty AS varchar (10)), 10)

 

select REPLICATE (‘0’, 10)

 

declare @qty varchar(12)

set @qty =‘Abdul Rehman’

select LEFT(@qty ,   CHARINDEX(‘ ‘, @qty)-1) + ‘—–‘ +  RIGHT(@qty,  LEN(@qty)-charindex(‘ ‘,@qty ) )



//////////////////////////////////////////////////////////////////////

CREATE TABLE employee(
9>    id          INTEGER NOT NULL PRIMARY KEY,
10>    first_name  VARCHAR(10),
11>    last_name   VARCHAR(10),
12>    salary      DECIMAL(10,2),
13>    start_Date  DATETIME,
14>    region      VARCHAR(10),
15>    city        VARCHAR(20),
16>    managerid   INTEGER
17> );

 

CREATE TABLE authors(
4>    au_id          varchar(11),
5>    au_lname       varchar(40)       NOT NULL,
6>    au_fname       varchar(20)       NOT NULL,
7>    phone          char(12)          NOT NULL DEFAULT (‘UNKNOWN‘),
8>    address        varchar(40)           NULL,
11>    zip            char(5)               NULL,
12>    contract       bit               NOT NULL
13> )

 

/////////

LOCAL,
Recompile

/////////////////////////////////////////////////

Row number

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, FirstName,
LastName, ROUND(SalesYTD,2,1) AS "Sales
YTD" FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD
<> 0;

 

TEMP TABLES

CREATE TABLE #project_temp
        (project_no CHAR(4) NOT NULL,
        project_name CHAR(25) NOT NULL)

SELECT project_noproject_name
       INTO #project_temp1
       FROM project

 

CREATE TABLE #config_out
(
    name_col    varchar(
50),
    minval      int,
    maxval      int,
     configval   int,
    runval      
int
)

 INSERT #config_out
     EXEC sp_configure

 
SELECT FROM #config_out

 

 

SELECT TOP BankerIDAVG(BillingTotal) AS AvgBillin
INTO #TopBankers
FROM Billings
GROUP BY BankerID
ORDER BY AvgBilling DESC

/////


CREATE TABLE ##RandomSSNs
(SSN_ID 
int IDENTITY,
 SSN 
char(9) DEFAULT  LEFT(CAST(CAST(CEILING(RAND()*10000000000)AS bigint)AS varchar),9))
 GO
INSERT ##RandomSSNs VALUES (DEFAULT)
INSERT ##RandomSSNs VALUES (DEFAULT)
GO

SELECT FROM ##RandomSSNs

 

 

SELECT * INTO #MyContacts
4FROM employee WHERE id = 1

 

 


 
CREATE TABLE ##CustomerSales
 (
   ID 
char (6) NOT NULL,
   Year       smallint NOT NULL,
   Sales      money    NOT NULL
)
 GO

drop table ##CustomerSales

////

 

7CREATE TABLE #tmpvar
8> (
9>   Variable varchar (25) NOT NULL
10> )
11>
12> DECLARE
13>   @Variable varchar (25)
14>
15> EXEC ('INSERT INTO #tmpvar VALUES (''my value'')')
16>
17SELECT
18>   @Variable = Variable
19FROM
20>   #tmpvar
21>
22>
23> PRINT @Variable
24> GO

(rows affected)

(rows affected)
my value
1>
2> DROP TABLE #tmpvar
3> GO

 

TABLE VAR

 

DECLARE @MyTable Table

   (

       OrderID      int,

      CustomerID   char(5)

    )

 

    INSERT INTO @MyTable

      SELECT OrderID,
CustomerID

       FROM Orders

       WHERE OrderID
BETWEEN 10240 AND 10250

    SELECT *

    FROM @MyTable

 

 

///////////

IF EXISTS(
5>     SELECT FROM sys.objects
6>     WHERE type = ‘FN’ AND schema_id = SCHEMA_ID(dbo‘)
7>     AND name = ufnCntCustomers‘)
8>     DROP FUNCTION dbo.ufnCntCustomers
9> GO

////

 

 

///////////

 

FUNCTION DEFINATION

 

CREATE FUNCTION dbo.udf_ProperCase(@UnCased varchar(max))
4> RETURNS varchar(max)
5> AS
6BEGIN
7>     SET @UnCased = LOWER(@UnCased)
8>     DECLARE @C int
9>     SET @C = ASCII(‘a’)
10>     WHILE @C <= ASCII(‘z’)
11>         BEGIN
12>             SET @UnCased = REPLACE( @UnCased, ‘ ‘ + CHAR(@C), ‘ ‘ + CHAR(@C-32))
13>             SET @C = @C + 1
14>         END
15>     SET @UnCased = CHAR(ASCII(LEFT(@UnCased, 1))-32) + RIGHT(@UnCased,LEN(@UnCased)-1)
16>     RETURN @UnCased
17END
18> GO

 

CREATE FUNCTION fn_factorial (@param decimal(38, 0) )

RETURNS decimal(38, 0)

 

 AS

 BEGIN

    IF (@param < 0
OR @param > 32) RETURN (0)

    RETURN (CASE

         WHEN @param
> 1 THEN @param  * dbo.fn_factorial(@param – 1)

         ELSE 1

         END)

 END

 GO

 

Format
a date

 

3CREATE FUNCTION MyDateFormat
4>          (@indate datetime,
5>           @Separator char(1)='-')
6> RETURNS nchar(20)
7> AS
8BEGIN
9>     RETURN
10>         CONVERT(nvarchar(20), DATEPART(dd, @indate))
11>         + @Separator
12>         + CONVERT(nvarchar(20), DATEPART(mm, @indate))
13>         + @Separator
14>         + CONVERT(nvarchar(20), DATEPART(yy, @indate))
15END
16> GO
1>
2SELECT dbo.MyDateFormat(GETDATE(), '*')
3> GO

 

 

SELECT INTO
5>   ID,
6>   Start_Date
7> INTO
8>   #OrderInfo
9FROM
10>     Employee
11>
12select from #OrderInfo

///////////

DECLARE @Value1 int

DECLARE @Value2 int

SET @Value1 = 55

SET @Value2 = 955

SELECT NULLIF(@Value1, @Value2)

//////////////////////////////////////////////////////////////////////////////////////////////////////

 

TRIGGERS

 

CREATE TRIGGER prevent_drop_triggers
       ON DATABASE FOR DROP_TRIGGER
       AS PRINT ‘You must disable "prevent_drop_triggers" to drop any trigger
       ROLLBACK

///////////

n   Disallow new Logins on the SQL instance
2CREATE TRIGGER trg_RestrictNewLogins
3> ON ALL SERVER
4> FOR CREATE_LOGIN
5> AS
6> PRINT ‘No login creations without DBA involvement.
7> ROLLBACK

/////////////

 

http://www.java2s.com/Tutorial/SQLServer/CatalogSQLServer.htm

http://www.java2s.com/Tutorial/SQLServer/CatalogSQLServer.htm

GENERAL FUNCTIONS

 

select ””+SPACE(100) +””

/////////////////////////////////////

select SUBSTRING(@qty, 3,5)

/////////////////////////////////////////////////

select SUBSTRING(@qty, 3,5)

/////////////////////////////////////////////////

SELECT * FROM A

select @@ROWCOUNT

 

//Right  left

DECLARE @FullName VarChar(25)

 SET @FullName = ‘George Washington’

SELECT LEFT(@FullName, 5)

//

CONVERT(VARCHAR,@intNumOrders)

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: