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.
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.
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.
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.
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.
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.
8) Let's test another business requirement: lookup of non-existent key should generate an error.
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.
Written by Montreal DBA Team