SQL Stored Procedures

29 05 2009

Apparently this is the STANDARD way to do stuff πŸ™‚

So, here’s the story… Microsoft has its own modified version of SQL (called T-SQL or Transact SQL)… this is what comes with those SQL Servers that get installed when you put Visual Studio… there is this neat tool called SQL Management Studio… Use it. Its a nice Graphical Interface. Saves you loads of time writing queries to do some basic stuff.. Apparently the newer 2008 SQL Managemet studio has debugging feature but 2005 doesnt 😦

Anyway, so in PHP I was used to writing queries in the php code itself.. I did that for my ASP.NET code too… only to read later on that stored procedures are the way to go and then saw a small sentence in the Software Requirement Specs that said “Standard security and error checking measures… 3)Stored procedures for SQL”

So, this is how it works. Instead of giving full access to the DB user, you give them access only to the procedures. And since you have written the procedure and they open up only a very limited window for meddling with stuff… its secure πŸ™‚

I am using the SQL logins with inbuilt windows authentication… one of the many advantages of being on a domain account

A few important things to remember… you pass values to the procedures through ASP.NET. Carefully specify the format field (if its nchar… coz apparently the AddWithValue() function does a String lenght of the passed char and puts that as nvarchar… ). Queries can be returned directly. No need to specify a spearate return variable etc. But you can if you want to. Just specify the direction as output (even in sql)




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: