Monday, April 26, 2010

Param datatypes with Sql Server Stored Procs

I tried several times to save and post this blog entry earlier today and it would NOT save. I have no idea what is wrong as I tried to email it to myself so I could do it later and I couldn't email it either. I'm thinking there was a bad character in my original text, but I pasted in into (and copied it out of notepad) so...  I'm not sure what is going on.  Sometimes these tools in the 'cloud' seem to be in a 'fog'.

Anyway, what I wanted to say was....
I've been working with stored procs lately in Sql Server.  My stored procs take search parameters that I wanted to be very flexible - strings with wild characters on either end using a LIKE in the SQL.  Originally in my declaration for the parameters I used a char datatype.  What I didn't realize was if the param passed in is shorter than the length you specified, a char datatype will automatically add placeholder characters to fill up the rest of the space. So, if  you declared a char with 9 characters and passed the SP a 3 character string, SQL Server would automatically add 6 more characters to it.  I was then trying to prefix and append the percentage sign (wild character) on either end of my param, but it was getting ignored.

In the end (with the help of my more DB inclined colleague, Karlson) I changed the datatype of my param to varchar and I could happily prefix and append wild characters to it.

Got another interesting business idea over the weekend - it's based on a few assumptions, and it's for the investment/financial industry.  The assumptions are:
- history repeats itself.
- the stock market, like history, is cyclical in nature
- investment funds are just as much marketing as they are statistical science
- people will buy anything that is marketed well, whether it provides them with tangible value or not - (the value of stock is all about perception of value)
The idea is simple - create a fund that is based on the cyclical nature of the stock market.  Predictable things happen when interest rates go up, or when commodities or high.  Convince people that you can harness the 'value' in the history of what happens to the stock market with certain indicators are up, and you'll have a winner fund.

No comments: