SQL Server Developer

An Introduction to SQL Server Scalar UDFs

One of the best features of SQL Server 2000 is its user-defined functions (UDFs). UDFs offer many of the benefits of both views and stored procedures, and more. Unlike views, UDFs can take zero or more input parameters and return a scalar value, or can even return a table. Unlike stored procedures, the output of […]

SQL Server Techniques for Creating a Web Reporting Page — Part 3

This article uses the table we created in Part 1, called CUBE. Data is usually normalized into columns. For example, suppose we have a table of appointments. The table consists of 2 columns, DATE and APPOINTMENT. A common request is to display the data in a monthly calendar format. That is “pivot” the columns into […]

SQL Server Techniques for Creating a Web Reporting Page — Part 2

In this article, we build upon our stored procedure and will demonstrate how to implement SORT and TOTAL row functionality. SORT Our sales manager is happy with our previous work. Now he requests the ability to sort the results, ascending and descending. In the web page, we provide an input select box where he can […]

Return Values: Getting Something Back from Your SQL Server Stored Procedures

You give so much to your database . . . sometimes you just want something back. What do you give your database?  Plenty. You give it new records (INSERT). You give it changes to existing records (UPDATE/DELETE). Every now and then, you just look around (SELECT). When you do these things, it is often essential […]

SQL Server Techniques for Creating a Web Reporting Page — Part 1

In this article, I’ll demonstrate how to give web users the ability to search and group data via web reporting pages –- using good old web scripting and SQL Server. No ETL/OLAP tool needed. Before we begin, I need to explain data warehousing. In general, as raw transaction level data accumulates, we summarize the data […]

How to Search for Date and Time Values Using Microsoft SQL Server 2000

Suppose you’re writing a query to find all the invoices that were written on January 6, 2003. You know from the control totals that 122 invoices were written that day. But when you run this query: SELECT * FROM InvoicesWHERE InvoiceDate = ’2003-01-06′ the result set is empty. What’s going on? How Dates and Times […]

How to Write SQL to Dynamically Script Mass INSERT Statement Scripts

The following is an article that describes a unique approach of scripting out the contents of a table. The reasons for doing so are many. The context that I will provide is one where you have a development environment where static tables exist for the purpose of storing application lookup data (i.e. a ZipCode table), […]

How to Fix the Problem When ADO Truncates Milliseconds from SQL Server Datetime Columns

If you create a SQL Server table that contains a datetime column and populate it with a GetDate() default then try to query it with ADO, you will discover that the data returned from datetime columns is returned without milliseconds. If you need milliseconds returned, this can be very annoying to deal with. For example, […]

Fun with Numbers in Transact-SQL Queries


Create and Manage SQL Server Stored Procedures using Transact-SQL

As we learn more about SQL Server and Transact-SQL, it is easy to become overwhelmed with the amount of information out there. I can recall hundreds of times when I have said, “I know there is a (stored proc, DBCC command, SQL statement, etc.) out there that does that, I just can’t remember its name.” […]