Which is better... This is mostly a matter of taste, depends on where you use this, and how you have your display code / data access code / storage layers.
I will explain a bit, trying to keep the answer short, and to try to give you a feel of when to use what.
A simple scenario: you use your database just to store and retrieve some simple data, for instance for a website. This data could contain some usage logs, or simple applications.
The SQL to get or store the data is very straight forward, and gives little reason to worry about errors, stability, performance.
Well be my guest, use some inline dynamically generated SQL. As long as you keep an eye on intrusion and sql injection. It is a good idea to limit repetition of code throughout the application, though.
A slightly different scenario could be the same simple data storage, but used for high volume data traffic, or for more frequent or more complex data reads. SQL server likes it better to avoid recompiling your query for each call. Easiest to do this is to put your sql nicely wrapped in a stored proc. But only if you feel confortable to do so.
You will get the great advantage of stored procedures in performance and maintenance when more complex database code is involved. Mostly you could have certain tasks done by code in the database like updating some data and cascading a change trough some other tables as defined by the business rules in the application. I like to have the data buisiness rules double checked in the database, using stored proc's.
Properly designed (I have never found those in real life...) applications would also do some checking of credentials, and also some error handling/fixing.
Personally I like to have my web table (or data grid) derive their data from a stored procedure. I find it easier to extend functionality this way and have the proper rules applied. When done properly, this approach makes it easier to build your applications. But some level of experience is required.
Currently I am involved in some ETL, data loading, transformation and preparation for some reporting tool. I do use stored procedures a lot to keep the code close to the database (where I think it should be) - also for ease of deployment. The code involved is usually much more than simple queries. It is easiest for me to do performance testing and improvement this way. And, I do actually dynamically generate SQL inside these stored procedures when needed.
Oh, keel an eye on SQL server 2005. You can do programming in languages other than SQL. I do advise you to only do so when you find a good reason for this, for example when the code is more about loops than data access. I understand you can gain good improvement in longer running stuff, but do check if it is worth all the work.
Good luck!