MS SQL Examples

//example string with pring (only one value can be printed)
PRINT 'An example string with '' escape of single quote';
GO

//creates value 24.85 in column HourlySalary
SELECT 24.85 AS HourlySalary;

//an example of nested select statements
SELECT (SELECT (SELECT 1350.75));
GO

//sql uses + - * /, also =, <>, <, <=, >, >=
//sql also accepts the bit operators: NOT ~, AND &, OR |, XOR ^
//TRUE = 1 = Yes, FALSE = 0 = No, NULL is for unknown, e.g. IF @i IS NULL


DECLARE @variablename datatype
SELECT @variablename = value 
//note that SET is the same as SELECT in this instance
//datatypes: bit, int, smallint, tinyint, bigint, decimal, float, 
//these can display more digits of precision, e.g. Decimal(6,3) = six 
//digits...
//money, smallmoney (+-214,000), datetime, char, 
//varchar(#of chars up to 8KB), text, nchar, nvarchar, ntext (all unicode)

Keyword Expression (conditional, e.g. IF condition)
BEGIN
    Statement line 1
    Statement line 2
END

DECLARE @DateHired As DateTime,
        @CurrentDate As DateTime
SET @DateHired = '1996/10/04'
SET @CurrentDate  = GETDATE()
IF @DateHired < @CurrentDate
    PRINT 'You have the experience required for a new promotion in this job'
GO

CASE Expression
    WHEN Value1 THEN Result
    WHEN Value2 THEN Result

    WHEN Value_n THEN Result

    ELSE default_result
END



DECLARE @Number As int
SET @Number = 1
WHILE @Number < 5
    BEGIN
    SELECT @Number AS Number
    SET @Number = @Number + 1
    END
GO




SELECT     statuscodename, statecode, statecodename
FROM         FilteredLead
WHERE     (statecodename = 'Disqualified')



HAVING  was added to SQL because the WHERE keyword could not be 
used against aggregate functions (like SUM), and without HAVING...
it would be impossible to test for result conditions.

The syntax for the HAVING function is:

SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value

- - -


SELECT     COUNT(statecodename) AS statecodename_Total, statecodename
FROM         FilteredLead
GROUP BY statecodename
HAVING      (statecodename = 'Disqualified')


Becomes fancier:

SELECT     COUNT(statecodename) AS statecodename_Total, statecodename, statuscodename
FROM         FilteredLead
GROUP BY statecodename, statuscodename
HAVING      (statecodename = 'Disqualified')
ORDER BY statuscodename

Adding "pre filter - users can filter data by dates etc."

SELECT     COUNT(new_countryidname) AS new_countryidname_Total, new_countryidname
FROM         FilteredLead AS CRMAF_FilteredLead
GROUP BY new_countryidname
ORDER BY new_countryidname

Two tables (related field)

SELECT     COUNT(CRMAF_FilteredLead.new_divisions2leadid) AS new_divisions2leadid_Total, CRMAF_FilteredLead.new_divisions2leadid, 
                      CRMAF_FilteredNew_Division.new_name
FROM         FilteredLead AS CRMAF_FilteredLead CROSS JOIN
                      FilteredNew_Division AS CRMAF_FilteredNew_Division
GROUP BY CRMAF_FilteredLead.new_divisions2leadid, CRMAF_FilteredNew_Division.new_name
ORDER BY CRMAF_FilteredLead.new_divisions2leadid


SELECT     COUNT(owneridname) AS CountOwneridname, owneridname
FROM         FilteredOpportunity AS CRMAF_FilteredOpportunity
GROUP BY owneridname


SELECT     SUM(estimatedvalue) AS Expr1, owneridname
FROM         FilteredOpportunity
GROUP BY owneridname


SELECT     COUNT(owneridname) AS CountOwneridname, SUM(estimatedvalue) AS SumEstimatedValue, owneridname
FROM         FilteredOpportunity AS CRMAF_FilteredOpportunity
GROUP BY owneridname
HAVING      (SUM(estimatedvalue) > 0)


Visual Basic Studio with SQL Server 2005...
You can also insert Static Text,  Variable/Functions, Pictures� e.g. Timestamp

="Report Processed Date: " & Globals!ExecutionTime.ToShortDateString() & " " & Globals!ExecutionTime.ToShortTimeString()



- - - - - - - - - - 
STORED PROCEDURE TO FIND WHICH TABLE(S) HOLDS A FIELD (sp_fieldinfo)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[sp_FieldInfo]'))
DROP PROCEDURE [dbo].[sp_FieldInfo]
Go
CREATE PROCEDURE sp_FieldInfo
(
@column_name nvarchar(384) = NULL
)
AS
SELECT

Object_Name(id) as "Table Name",
rtrim(name) as "Field Name"
FROM
syscolumns
WHERE
Name like @column_name