SQL
Server
0. 2020 Stored
Procedures
1. Table Variables
2. Select Case
3. Cursor
New ID: select newid() as NewID
‘Join two
table problem I had to solve…
select
items1.*,i2.Series,i2.Content
from items1 left join
(select
items2.id_Item,items2.Series,items2.Content from items2 where items2.Series = 2) i2
on
items1.ID_Item = i2.ID_Item
Sample:
where t.ID_Sites_Group
= @ID_Sites_Group and t.Active = 1 and (s.State_Abbrv = @State or @State is null) and (t.Site_Name like @SiteName
or @SiteName is null)
***********************************************************************************************************************
Great Sample
Case Statemet
***********************************************************************************************************************
WHERE (Candidate_Logged_In_Date
> '1/1/2013') and
CASE WHEN
not @Name_last is null AND Users.Name_Last LIKE '%' +
@Name_Last + '%'
THEN
1
WHEN not @UserId is null AND Users.UserId LIKE '%' + @UserID + '%'
THEN
1
WHEN not @Email_Address is null
AND Users.Email_Address LIKE '%' + @Email_Address +
'%'
THEN
1
WHEN not @ID_Score_File is null
AND Score_File.id_score_file = @ID_Score_File
THEN
1
ELSE
0
END
= 1
***********************************************************************************************************************
***********************************************************************************************************************
*********************************************************************************************************
*********************************************************************************************************
VB.Net SQL Paramaters
'*******************************************************
Update SQL Stored Procedure
'*******************************************************
Dim
objConn As New
SqlConnection("Server=PEAK10-SQL01; Database=V014U07NUI; user
id=V014U07NUI_SMTOnline_2; password=abcxyz;")
Dim
objcommand As New
SqlCommand
objcommand.CommandType =
CommandType.StoredProcedure
objcommand.CommandText = "spQuickFixUpdateUser"
'*******************************************************
Dim
prm As New
SqlParameter
prm.ParameterName = "@ID_User"
prm.SqlDbType = SqlDbType.Int
prm.Direction =
ParameterDirection.Input
prm.Value = Val(txtIDUser.Text)
objcommand.Parameters.Add(prm)
'*******************************************************
'*******************************************************
prm = New
SqlParameter
prm.ParameterName = "@Name_last"
prm.SqlDbType = SqlDbType.NVarChar
prm.Direction = ParameterDirection.Input
prm.Value = txtName_Last.Text
objcommand.Parameters.Add(prm)
'*******************************************************
objcommand.Connection = objConn
objConn.Open()
objcommand.ExecuteNonQuery()
objConn.Close()
MsgBox("Record Updated.")
'*******************************************************
Select SQL Stored Procedure
'*******************************************************
Dim objConn As New
SqlConnection("Server=PEAK10-SQL01; Database=V014U07NUI; user
id=V014U07NUI_SMTOnline_2; password=abcxyz;")
Dim
objCommand As New
SqlCommand
Dim
objDa As New
SqlDataAdapter
Dim
objDs As New
DataSet
Dim
sSQL As String
objCommand.CommandType =
CommandType.StoredProcedure
objCommand.CommandText = "spQuickFixSitesSeats"
Dim
prm As New
SqlParameter
prm.ParameterName = "@ID_Sites_Seat"
prm.SqlDbType = SqlDbType.Int
prm.Direction =
ParameterDirection.Input
prm.Value = ID_Sites_Seat
objCommand.Parameters.Add(prm)
objCommand.Connection = objConn
objDa.SelectCommand = objCommand
objDa.Fill(objDs, "ss")
objConn.Close()
*********************************************************************************************************
*********************************************************************************************************
Shrink Log File...
ALTER DATABASE
[COTO] SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([COTO_LOG], 1)
GO
ALTER DATABASE [COTO] SET RECOVERY FULL
GO
Rename a database..
First take db
offline then back online (to remove any stray users), then from query analyzer,
Exec sp_renamedb
‘DatabaseName’, ‘NewDatabaseName’
Convert DateTime to Date…
CONVERT(datetime, CONVERT(varchar(11), SMT_ScoreFile.ExamDate))
AS examdate
Remove Single User Mode...
From Query Analyzer interface...
exec sp_dboption 'testdb01', 'single user',
'FALSE'
Cross Database Query…
SELECT Requests.Title, Requests.Description, SMTScoring.dbo.SMT_Clients.ClientCode,
Requests.Date_Created, Requests.ID_Requests
FROM Requests INNER
JOIN
SMTScoring.dbo.SMT_Clients ON SMTScoring.dbo.SMT_Clients.id_client
= Requests.ID_Client
WHERE (SMTScoring.dbo.SMT_Clients.CompanyCode
= 'ess') AND (SMTScoring.dbo.SMT_Clients.ClientCode
LIKE 'nic%') AND
(Requests.Date_Created >= CONVERT(DATETIME,
'2010-01-01 00:00:00', 102))
Reseed identity field..
DBCC
CHECKIDENT('mytable', RESEED, 0)
Instring…
SELECT CandidateName
FROM
SMT_ScoreFile
WHERE
(CHARINDEX('ELROY', CandidateName) > 0)
Change NText To Text for
searching…
AND (CONVERT(varchar(2000),
Exams_Bank_ItemTexts.Item_Text) LIKE '%red;%')
***********************************************************************
Dynamic sql…
declare
@ExamCode nvarchar(30)
declare @SiteCode nvarchar(30)
Set
@examcode = '7004035111'--null
Set
@SiteCode = 'SMTCBT' -- null
Select examcode,sitecode,examdate from smt_scorefile where examdate
>='2/1/2010' and examdate <='2/15/2010'
and
CASE
WHEN @ExamCode IS NULL THEN 1 -- if examcode is null then return all the records.
WHEN @ExamCode = smt_scorefile.examcode
THEN 1 -- if examcode is not null then only return
records where @examcode = smt_scorefile.examcode
else 0
END
=1
and
Case
WHEN @SiteCode Is Null then 1
WHEN @SiteCode = smt_scorefile.sitecode
then 1
else 0
end =1
***********************************************************************
**********************************************************************
Dynamic sql
Another way…
declare @State varchar(25)
declare @School varchar(25)
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Set @State = 'wa'
set @School = '4620'
Set @SQLQuery = 'select state,examdate,school from
smt_scorefile where 1 = 1 '
If @State Is Not Null
Set
@SQLQuery = @SQLQuery + ' And (State = @State)'
if @School is not null
Set @SQLQuery = @SQLQuery + ' and (school =@School)'
Set @ParamDefinition = '@State
NVarchar(3),
@School Nvarchar(4)'
Execute sp_Executesql @SQLQuery,
@ParamDefinition,
@State,
@School
***********************************************************************
***********************************************************************
COALESCE…
…and SMT_ScoreFile.SiteCode
= COALESCE(@InSiteCode,SMT_ScoreFile.SiteCode)
***********************************************************************
How to restore a SQL database
from Database A backup to New Database B...
This seems to work on 11.14.2020..
RESTORE DATABASE stevetmp FROM DISK = 'D:\data\temp\bak.bak'
with replace
In SQL Analyzer….
RESTORE FILELISTONLY
FROM DISK = 'D:\testadmn\can\isc2sql\BackedUpSQLDatabase.bak'
Then in SQL Analyzer…
RESTORE DATABASE NewDataBaseName
FROM DISK =
'D:\testadmn\can\isc2sql\BackedUpSQLDatabase.bak '
WITH REPLACE, MOVE 'OriginalDatabaseName_Data' TO
'd:\all_sql_server\2000\MSSQL\data\NewDataBaseName.MDF',
REPLACE, MOVE ''OriginalDatabaseName_Log' TO
'd:\all_sql_server\2000\MSSQL\data\NewDataBaseName.LDF'
SELECT TOP 1 dbo.Exams_Bank.ID_Exams_Bank,dbo.Exams_Bank.Item_Bank_Ref,
ABS(1 - dbo.Exams_Bank.ThetaMax) AS ABSTheta,dbo.Exams_ContentAreas.ID_Exam
FROM dbo.Exams_Bank INNER JOIN dbo.Exams_ContentAreas ON dbo.Exams_Bank.ID_Exams_ContentArea
= dbo.Exams_ContentAreas.ID_Exams_ContentArea
WHERE
(dbo.Exams_ContentAreas.ContentArea_ID = 1) AND
(dbo.Exams_ContentAreas.ID_Exam = 56)
AND (dbo.Exams_Bank.ID_Exams_Bank NOT IN (SELECT id_exams_bank FROM score_file_responses
WHERE id_score_file = 24533))
******************* SQL Server Session State Setup
*******************************************
From: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
aspnet_regsql.exe -S PEAK10-SQL01\PEAK10SQL2008 -E -ssadd -sstype p
Sample WebConfig Session State Modification....
<sessionState allowCustomSqlDatabase="true"
mode="SQLServer" sqlConnectionString="initial
Catalog=ASPState;user id=SMTBankUser;password=xxxxxxx;Data
Source=PEAK10-SQL01\PEAK10SQL2008;Connect Timeout=60;" />
******************************************************************************************
Linked Server Sample...
SELECT top 10 s1.cbtidscorefile,s1.candidatename
FROM SMT_ScoreFile as s1
JOIN
[PEAK10-SQL01].V014U07NUI.dbo.Score_File as p1
ON s1.CBTIdScoreFile = p1.ID_Score_File
WHERE (s1.id_Client = 1) AND (s1.ExamDate >= '3/1/2012')
ANOTHER
EXAMPLE…
SELECT smtsf.CandidateID, smtsf.Status, smtsf.Name_First, smtsf.Name_Last, SMT_Keys.TestName, smtsf.ExamDate, smtsf.SiteCode, smtsf.TestCode,
smtsf.TestForm, SMT_ScoreFile_Parts.PartTot, SMT_ScoreFile_Parts.PartRaw, SMT_ScoreFile_Parts.PartScale, SMT_Keys_Part_Names.Part_Name, SMT_ScoreFile_Parts.ControlNumber,st1.Site_Name,ss1.ID_Sites_Seat,ss1.ID_Site,sg1.Sites_Group_Name,st1.City,stat1.State_Abbrv,con1.Country
FROM
SMT_ScoreFile as smtsf
INNER JOIN SMT_ScoreFile_Parts ON smtsf.id_ScoreFile = SMT_ScoreFile_Parts.id_scoreFile
INNER JOIN SMT_Keys ON smtsf.ExamCode = SMT_Keys.ExamCode
left JOIN V014U07NUI.dbo.Score_File AS p1 ON smtsf.CBTIdScoreFile = p1.ID_Score_File
left JOIN V014U07NUI.dbo.Sites_Seats as ss1 ON p1.ID_Sites_Seat = ss1.ID_Sites_Seat
left JOIN V014U07NUI.dbo.Sites as st1 ON st1.ID_Site = ss1.ID_Site
left join V014U07NUI.dbo.Sites_Groups as sg1 on sg1.ID_Sites_Group = st1.ID_Sites_Group
inner join SMT_Keys_Part_Names ON SMT_ScoreFile_Parts.ControlNumber = SMT_Keys_Part_Names.ControlNumber
AND SMT_Keys.id_key = SMT_Keys_Part_Names.id_Key
left join V014U07NUI.dbo.States as stat1 on stat1.ID_State= st1.ID_State
left join V014U07NUI.dbo.countries as con1 on con1.ID_Country= st1.ID_Country
WHERE testname <> 'cdms practice exam' and
(smtsf.Inactive <> 1) AND (smtsf.ExamDate >= '1/1/2022') AND (smtsf.ExamDate < '12/1/2022') AND (smtsf.id_Client = 427)
ORDER BY smtsf.CandidateID, smtsf.ExamDate,ControlNumber