Fransiscus Setiawan .NET Blog

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 [...]

This feature has been sometime in SQL Server 2005 in SQL Server 2000 you normally use @@TRANCOUNT to detect any exception but now in SQL Server 2005 you can use try catch block.
In this snippet, there are 2 INSERT statement and 1 UPDATE statement. I’m expecting when there is any failure (e.g the UPDATE statement [...]

UPDATED: I’ve added one function to write from BLOB in SQL Server table to the disk straight away
I thought this article might be useful for anyone that wants to implement .NET code to SQL server level. In this case I really need CLR because I want to do compression of images and I believe it’s [...]

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 [...]

I got a column with Data Type of Image in SQL Server. What I would like to do is to omit this data to a FileStream or to a file. I tried to read the data using LINQ and I found the data type detected for that particular column is System.Data.Linq.Binary. I was expecting it [...]

I’m trying to implement DateDiff in LINQ. I would like to get all records with the same date and ignoring the timestamp.
Here is my code snippet which doesn’t work

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public int tblExpReceiptFileUniqueID(string chCreateStaffCode, string vcFileName,
[...]

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 [...]

This is a query to find all the indexes in your table including when it was last updated
Find all indexes on all tables

1
2
3
4
5
6
7
8
9
SELECT
OBJECT_NAME(OBJECT_ID) AS ‘Table Name’,
[name] AS ‘Statistic’,
STATS_DATE(object_id, index_id) AS ‘Last Updated Statistics Date’
FROM
sys.indexes
ORDER BY
STATS_DATE(object_id, index_id)
DESC

Find all indexes on a particular table

1
2
3
4
5
6
7
8
SELECT
OBJECT_NAME(OBJECT_ID) AS ‘Table Name’,
[name] AS ‘Statistic’,
STATS_DATE(object_id, index_id) [...]

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE FUNCTION [dbo].[SplitCSV](@CSV text)
– Returns a table with txtValue column
RETURNS @OutTable TABLE(txtValue [...]

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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, [...]