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

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

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

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

Basic Requirements:
1. Have the MSDTC(Distributed Transaction Coordinator) windows service running on your machine
2. Be talking to a SQL 2000 or 2005 Database configured likewise
3. Run this registry script to fix the windows XP SP2 that was causing MSDTC to fail (save as “.reg”)
Registry Script

Windows Registry Editor [...]

This is very important for the most web developers. This stored procedure accept parameter of page index and page size. By using this paging, you don’t need to store everything in your viewstate which in the end will cause performance suffer. This will load up the necessary result based on the page index and page [...]

I’ve got a question this morning about how to find a particular query in a huge list of stored procedure. It’s not that hard, what you need to do is to use SYS object and sys.procedures basically contains all the stored procedure under the active database that you are using. This is the query [...]

I tried to create an average in sql server by dividing two figures. Once I got the error of “Divide by zero error occured”, this is caused by dividing the figure by null value, to handle the error we can use “NULLIF”

1
2
3
4
5
6
7
8
9
10
11
DECLARE @ThisMonthAvg decimal(8,2)
SET @ThisMonthAvg = @ThisMonthPlayers / NULLIF(@ThisMonthEvents, 0)
 
DECLARE @LastMonthAvg decimal(8,2)
SET @LastMonthAvg = @LastMonthPlayers [...]