Text Pay Me

I just wanted to bop in here real quick and let everyone know about this really neat way to pay for things called Text Pay Me. The service lets you send people money over your cellphone by way of text messaging. We here at Grinn Productions use it all the time to pay each other back for our split of the lunch bill. Oh, and they pay you $5 for signing up!

Read: Text Pay Me.

While We're on the Subject...

4 Guys from Rolla has a really great way of finding and deleting duplicate rows in a table. It doesn't require the use of temporary tables or long-winded procedures like some of the more common methods. If you run into this problem, remember who told you about the fix!

Read: http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtml

Stored Procedures with SQL Server 2005

Recently we've upgraded our MS SQL Server from 2000 to 2005, 64-bit Edition. This was mainly due to the limited compatibility of 2000 with 64-bit versions of Windows. Shortly thereafter we needed to re-write the SQL in some of our reports and optimize them for speed.

The reports were still in Beta and we had not yet set them up with Stored Procedures. Part of that optimization involved converting the SQL for those reports to use Stored Procedures. This entry will help those unfamiliar with Stored Procedures learn some of their benefits, capabilities, and limitations.

You can think of SQL stored procedures as a placeholder for a long (or just long-running) SQL query. So instead of:

SELECT blah.idBlah, blah.helloKitty, blah.otherField FROM blah INNER JOIN
something ON something.idBlah = blah.idBlah WHERE blah.cookie='cake'

you could just have:

EXECUTE usp_blahCookies

Naturally, you must specify what usp_blahCookies means. SQL 2005 makes this easy. In SQL Server Management Studio, under the Object Explorer, expand the database you want to add the Stored Procedure to, expand Programmability, right-click on Stored Procedures and select New Stored Procedures.... Management Studio will generate a nice little template for you to work with, like so:

-- ================================================
-- Template
generated from Template Explorer using:
-- Create Procedure (New
Menu).SQL
--
-- Use the Specify Values for Template Parameters
--
command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
--
This block of comments will not be included in
-- the
definition of the
procedure.
--
================================================
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author:
<author,,name>
-- Create date: <create>
--
Description:
<description,,>
--
=============================================
CREATE PROCEDURE
<procedure_name,>
-- Add the parameters for the
stored
procedure here
<@Param1, sysname, @p1>
<datatype_for_param1,>= <default_value_for_param1,>,
<@Param2, sysname, @p2> <datatype_for_param2,>=
<default_value_for_param2,>
AS
BEGIN
-- SET NOCOUNT ON
added to prevent extra result sets
from
-- interfering with SELECT
statements.
SET NOCOUNT ON;
-- Insert
statements for procedure
here
SELECT <@Param1, sysname, @p1>,
<@Param2, sysname,
@p2>
END
GO

All you need to do is replace the bracketed fields with what you want, and you're good to go. Here's a quick breakdown of what we have:

<author,,name>, <create>, <description>- Use these fields to document the origin and purpose of this stored procedure
<procedure_name,>- The name of this stored procedure. In our example it would be usp_blahCookies. The usp_ is optional, but a good idea. It stands for User Stored Procedure and helps distinguish this as a user-specified stored procedure as opposed to a system s.p., table, or function.
<@Param1, sysname, @p1> <datatype_for_param1,>= <default_value_for_param1,>,... - Any parameters you wish to pass to your s.p. For example:

An integer parameter called taco:
@taco int

Two integers, and a varchar of the length of 50:
@taco int, @burrito int, @curry varchar(50)

Two integers, the second one having a default value of 5:
@taco int, @burrito int = 5


SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> - The SELECT, INSERT, or UPDATE statement(s) or procedure(s) you wish to execute. This is where our "SELECT blah.idBlah, blah.helloKitty, blah.otherField FROM blah INNER JOIN something ON something.idBlah = blah.idBlah WHERE blah.cookie='cake'" would go.

Stored procedures can greatly increase the speed and performance of your database applications. Perhaps even more importantly, they help to enforce separation of your data layer from your code.

This is by no means an end-all tutorial on Stored Procedures. However, I hope someone out there finds it useful. Here, have some links:

What MS has to say about Stored Procedures in SQL 2000
4 Guys on Stored Procedures
Another reason to use SP's: To prevent SQL injection

Once Upon a Time...

This blog is here for us to record any developments in Grinn Production's projects that we feel may be of interest to other software developers or general technophiles. We work on a lot of different stuff, from .Net web-based applications and Smart Client technologies to VoIP and Astrisk to cellphone "hacking".

If we find it generally interesting and worth your time to hear about, we'll post it here.