Return Select As Boolean

In this quick little post, I would like to document how to travers a SQL SELECT statement to return back a BIT/BOOLEAN. Suppose, you want to return true of false, based on a condition and your application can simply read this BIT/BOOLEAN and show appropriate view/response to end user.


SELECT [Account].Balance,

CAST(CASE

WHEN [Account].Balance - @Value > 0

THEN 1 ELSE 0

END AS Bit)

AS MoneyAvailable

FROM [dbo].[UserAccounts] [Account]

WHERE [Account].[UserId] = 13

The above statement will return a nice little one column ‘MoneyAvailable’ with a 1 or a 0 to denote if that account has available balance or not. Good stuff.

Advertisements

Connection Strings Without Web Config in Asp.Net

In this post, I am writing how to employ and utilise Asp.Net Connection Strings in when you don’t have the all-important Web.Config file at your disposal and you are disabled from using the Asp.Net ConfigurationManager class to establish the connection string.

Please review the link below to ensure you are using right Connection String format fitting your development environment and SQL instance.

Reference: http://www.connectionstrings.com/sql-server-2008

For establishing SQL Server Connection, running SQL Query / Stored Procedure you would require a Connection String. The code below uses a declarative connection string where Web.Config is not available.


Dim SqlConnectionString As String = "Server=SQLSERVER;Database=DB;User ID=Username;Password=password"
Dim ConnStringSettingObj As ConnectionStringSettings = New ConnectionStringSettings("constr", SqlConnectionString)

Dim path As String = ConnStringSettingObj.ConnectionString
Dim conn As SqlConnection = New SqlConnection(path)

Dim cmdText As String = "StoredProcedure_OR_SQL_Query"
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)

cmd.CommandType = CommandType.StoredProcedure
Dim _Param As SqlParameter = New SqlParameter("_SqlArgumentIfAny", SqlDbType.VarChar)
_Param.Value = "something"

cmd.Parameters.Add(_Param)

' If data return is required / otherwise ignore
Dim adaptr As SqlDataAdapter
Dim ds As New DataSet

Try
conn.Open()
adaptr = New SqlDataAdapter(cmd)
adaptr.Fill(ds)
Catch ex As Exception
Throw New ConfigurationErrorsException
Finally
conn.Close()
End Try

Creating TimeSlots with SQL DateTime Column

On this quick post, I want to memo how I used a SQL Temporary Table to build a temporary data store of half hour time slots within a 24 hour day on either a selected date or current date.

First I create a temporary table, and then I use two DateTime variables (Timeslots & Counter). Then using the DateAdd() SQL Function, I set the variables to midnight 00:00 until 00:00 next day for a given date or current date.

For example, 21 Dec 2012 00:00 am to 22 Dec 2012 00:00 am to get a perfect 24 hours

DROP TABLE #Times
CREATE TABLE #Times(HalfHourGaps DateTime)
DECLARE @TimeSlots DateTime
DECLARE @Counter DateTime

SET @TimeSlots = getdate()
SET @Counter = getdate()

SET @TimeSlots = dateadd(dd, 0, datediff(dd, 0, @TimeSlots))
SET @Counter = dateadd(dd, 1, datediff(dd, 0, @Counter))

WHILE (@TimeSlots <= @Counter)
BEGIN
INSERT INTO #Times(@TimeSlots)
SET @TimeSlots = dateadd(minute, 30, @TimeSlots)
END

SELECT HalfHourGaps FROM #Times

Now, when I obtained 30 minutes timeslots in temporary table, I can now check against any existing DateTime column in SQL table to see if datetime column value exists or matches in this temporary table.

Using Temporary Tables in SQL

This is how you can use SQL Temp Tables for doing some operations that might require a temporary table. A temporary table can be useful if you just want to have a data store of values you just require temporarily and not permanently sitting as a dedicated SQL table.

In this example, I am creating a temporary SQL table called tb1 with one column of type integer that will hold values from 0 to 10. This can be handy if you want to compare values either returned from a SQL Function or SQL Stored Procedures. You can also use temporary table to derive some values without actually changing the values in a SQL Table.

DROP TABLE #tempo1
CREATE TABLE #temp01(ID int)

DECLARE @i AS int
SET @i = 0

WHILE (@i < 10)
BEGIN
INSERT INTO #temp01 VALUES(@i)
SET @i += 1
END

SELECT * FROM #temp01

Naming Convention for Stored Procedures

While creating stored procedures, it is imperative you name them intuitively corresponding to the process they do.

I have learnt that a good naming convention really makes it easy to find the stored procedure you are looking for and for other team of developers to debug, modify or extend your existing stored procedures.

Needless to say, good naming convention is also best practice in software development process.

Here is how I like or prefer to follow

[DbName]_[Object]_[Action/Process]

Here is an example,

DbApplicants_Products_Add
DbApplicants_Products_Update

Another common practice is to add sp (stored procedure) or something bespoke instead in the front, followed by object and then full action name for instance,

sp_Product_AddNewProduct
sp_Product_UpdateExistingProduct
CMS_Product_Create
CMS_Product_Update

I suppose the best way is to have a naming convention than to not have one.

Get Sql Datetime Column without Timestamp

Sometimes, you would want to obtain only the ‘date’ part of a SQL datetime timestamp column.

Normally, your datetime column will hold dates in the following format.

2012-04-17 20:18:02.687

You can do the following to remove its time section

SELECT CAST(YourColumnName AS varchar(12)) AS DesiredColumnName from YourTableName where YourColumnName > ’2012/05/01′

This will result in the following

Feb 21 2002

It renders date in a more readable format, great when presenting dates on your website using either GridView or Repeater control.

Hope this is helpful.