Tag: TSQL Page 1 of 2

I had a problem where I need to restore database using shared folder on the network. I tried to copy it locally to my local harddrive but then the connection is not stable enough. Hence I decided to create a script

Single Media

USE MASTER
DECLARE @fromdatabase varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql

Multiple Media(Sometimes the backup source is stripped multiple files hence if you try to run it with above script you will get this error “The media set has 2 media families but only 1 are provided. All members must be provided.”)

USE MASTER
DECLARE @fromdatabase varchar(50)
DECLARE @fromdatabase2 varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
SET @fromdatabase2 = 'DailySupport-2'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ', DISK = N''' + @BackupPath + '' + @fromdatabase2 + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql

Here is the scenario, You have a stored procedure that builds dynamic insert SQL which means the “INSERT” statement is being composed on the fly based on the table name but at the same time you need to get SCOPE_IDENTITY or the last identity number inserted for further processing (e.g History table)

	DECLARE @nvcSQL			NVARCHAR(MAX)
	DECLARE @pvcTableName	VARCHAR(255)
	DECLARE @pvcColumn		VARCHAR(255)
	DECLARE @pvcValue		NVARCHAR(MAX)

	--This is used to actually get the primary key for identity record
	DECLARE @dvcPrimaryKey VARCHAR(255)
	SET @dvcPrimaryKey = ''

	DECLARE @dintPrimaryKey INT
	SELECT @dvcPrimaryKey = ISNULL(NAME,'') FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = @pvcTableName AND Is_Identity = 1


	-- Only execute when there is a @pvcValue.
	IF @pvcValue  ''
	BEGIN
		SELECT @nvcSQL = 'INSERT INTO ' + @pvcTableName + '(' + @pvcColumn+ ') VALUES (' + @pvcValue + ')'
		SELECT @nvcSQL = @nvcSQL + ';' + 'SELECT @dintPrimaryKey = SCOPE_IDENTITY()'
		--PRINT @nvcSQL

		EXEC sp_executesql @query = @nvcSQL, @params = N'@dintPrimaryKey INT OUTPUT', @dintPrimaryKey = @dintPrimaryKey OUTPUT
		--EXEC (@nvcSQL)
	END

	IF @dvcPrimaryKey  ''
	BEGIN
		SELECT (@dvcPrimaryKey + ' = ' + CAST(@dintPrimaryKey AS VARCHAR(10)))  AS PrimaryKey
	END
	ELSE
	BEGIN
		SELECT '' AS PrimaryKey
	END

Question: why do we need to use sp_executesql instead of EXEC?because EXEC just execute a SQL command without returning/expecting any result from it

This is the case:
I have a table and basically this table has many redundancy. The solution is to create a link table and this table will contains record association. So what I need to do is to get all the ID’s Populated to the link table and link it to a single unique record on the original table. The logic that I used is to get the value from the column based on the lowest ID.

INSERT INTO tblExpReceiptFileAssociation(intExpenseDtlId, intFileID)
	SELECT te.intExpenseDtlID, ta.LinkFileID
	FROM tblExpReceiptFile te
	INNER JOIN
		(
			SELECT vcFileName, bintFileSize, chCreateStaffCode,
				   CONVERT(VARCHAR,sdCreateDate,101) as DateCreated,
				   MIN(intFileID) as LinkFileID
			FROM
				tblExpReceiptFile
			GROUP BY
				vcFileName, bintFileSize,
				chCreateStaffCode, CONVERT(VARCHAR,sdCreateDate,101)
		) ta
	ON
		te.vcFileName = ta.vcFileName
	AND
		te.bintFileSize = ta.bintFileSize
	AND
		te.chCreateStaffCode = ta.chCreateStaffCode
	AND
		CONVERT(VARCHAR,te.sdCreateDate,101) = ta.DateCreated

How to check whether the temp table is exists on the memory or not?The reason why you need this is because your stored procedure can throw the error when you try to drop a temp table which is not exists anymore on the memory. The best practice to drop a temp table is normally to check the existence of the table on the memory then we drop the table if it is exists

--Drop the table after usage
IF object_id('tempdb..#tmpStandardCostRate') IS NOT NULL
BEGIN
   DROP TABLE #tmpStandardCostRate
END

I’ve got this SQL Function to parse comma separated value into a table. We need this function when our application commit separated comma value in a text. We normally do this when we want to send an array of value to the SQL server

CREATE FUNCTION [dbo].[SplitCSV](@CSV text)
-- Returns a table with txtValue column
RETURNS @OutTable table(txtValue text)
AS
BEGIN

declare @currentposition int
declare @nextposition    int
declare @lengthOfString  int

-- Assign the starting position of the string
SET @currentposition = 0
 -- The reason for this is to force entrance into the while loop below.
SET @nextposition = 1

WHILE @nextposition > 0
BEGIN
-- Assign the next position to be the current index of ',' + 1
SELECT @nextposition = charindex(',', @CSV, @currentposition + 1)

-- In here we need to find 2 things. The position of the ','
-- and the length of the string segment in between.
SELECT @lengthOfString = CASE WHEN @nextposition > 0
       THEN @nextposition
       ELSE DATALENGTH(@CSV) + 1
       END - @currentposition - 1

--After the length and position is found all we need to do
--is take the substring of the string passed in.
INSERT @OutTable (txtValue)
       VALUES (substring(@CSV, @currentposition + 1, @lengthOfString))

--Set the current position to the next position
SELECT @currentposition = @nextposition
END
RETURN
END

Usage in SQL Query

SELECT
	VenueID
FROM
	Venues
WHERE
	PostCode
IN
	(SELECT CAST(txtValue as VarChar) FROM dbo.splitcsv(@PostCode))

How to do order by in your paging using ROW_NUMBER() feature on SQL Server 2005

SELECT a.CurrentPrice, a.LotQuantity, a.IsAuction,
	a.AuctionID, a.AuctionName, a.DateStart, a.DateFinish,
	a.StartingPrice, a.ReservePrice, a.FixedPrice, a.BidIncrement,
	p.ProductID, p.CategoryID, p.BrandID, p.ProductName, p.ContentID, p.ThumbImageID,
	Row_Number() OVER
	(
		ORDER BY
		CASE
			WHEN @OrderBy = 'Price_ASC'	THEN CurrentPrice
			WHEN @OrderBy = 'ClosingSoon_ASC'	THEN ISNULL(DateFinish, DATEADD(DAY, 100, GETDATE()))
		END ASC,
		CASE
			WHEN @OrderBy = 'Price_DESC'		THEN CurrentPrice
			WHEN @OrderBy = 'ClosingSoon_DESC'	THEN ISNULL(DateFinish, DATEADD(DAY, 100, GETDATE()))
		END DESC,
		CASE
		       WHEN @OrderBy = 'Name_ASC'			THEN AuctionName
		END ASC,
		CASE
		       WHEN @OrderBy = 'Name_DESC'			THEN AuctionName
		END DESC
		) AS RowNum
FROM Auction a
	INNER JOIN Products p ON a.ProductID = p.ProductID
	INNER JOIN [Content] c ON p.ContentID = c.ContentID