Database.ca SQL Server Blog

My Blog

Tuesday, March 18 2014

Keywords: t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

Quick & easy "Key Value" starter pack with table and Get/Set stored procedures, SQL Server version

You could use key value tables to stored any kind of system or app level parameters you require. Almost all serious applications I have seen had used a form of key value table...and with the exception of a few, the great majority had a simple schema for storing these values...simplicity is a good principle in software design.

 

I am going to walk you through easy steps to create a key value sub-system in your SQL database. You will find a fully functional T-SQL sample downloadable at the bottom of this page. I have provided an Oracle/PLSQL version of this article as well in a separate article.

 

Here are the assumptions / business requirements for my app:
 

  • Key is varchar(100) and its size is more than enough for this app.
  • Value is varchar(1000) and its size is more than adequate for this app.
  • My app will not be storing any Unicode key or value. If your does, change the varchar to nvarchar.
  • Null or empty strings values will not be accepted neither as a key, nor as a value.
  • If you look up a non-existent key, an error is raised. I want my middle-tier to be aware that the key did not even exist in the first place...maybe the app was doing something wrong.
  • Key must be unique.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

1) Launch SQL Server Management Studio (SSMS) and connect to a database of your choice where you will be creating these objects. If you do not have a test database to play around with...create a new database.
 
2) Download the zip file on the bottom of this page. Extract the files. Open all the .sql files with SSMS. Run the script in "keyvalue table.sql" to create the keyvalue table. notice the key is the primary key of the table...this will make sure that a unique clustered index will be created on the key column...making the key lookups ultra fast...and will also assure the uniqueness of keys. Remember that uniqueness was one of our business requirements.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

3) Create the SetParameter stored proc now. You should get a message on the bottom that "command(s) completed successfully". Note that Null or empty string values are not being accepted into keyvalue table.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

4) Create the GetParamter stored procedure. Note that if the looked up key is non-existent in the keyvalue table...then an error will be raised. This is intentional. Unless otherwise required by an app...I would like the calling application to know that the key did not exist in the table in the first place...for many apps this could be an indication of something wrong.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

5) A quick test of SetParameter shows us that it works easily and instantaneously.
 
6) Let's do a Select on keyvalue table, to see with our own eyes that the record we just inserted with SetParameter did infact get inserted.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

7) Now that we are 100% assured that the newly inserted record does exist...let's test our GetParameter stored procedure. If all is done well..you should get 'some value' returned to you.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

8) Let's test another business requirement: lookup of non-existent key should generate an error.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

9) And finally, let's test the integrity of our keyvalue table...by trying to insert a duplicate key.
 
We are done! You could take this fully functional starter-kit and build on top of it as you require.

 

t-sql, sql server, key value, table, lookup, parameter, system parameter save load, stored procedure

Written by Ramin Haghighat