Advanced Stored procedure...

Hi Hope the following code snippet will help you ! use dynamic query.... declare @sqlstmt varchar(100) set @sqlstmt = 'SELECT * FROM .... WHERE '+@someVar +'='+@someParam exec sp_executesql @sqlstmt (note: refer SqlSErver online book for exact syntax...) - Purushotam

Thanx for your message... I totally agree with you ! I have many tables and complexity in relations so i have to use code only components. Finally i will probably choose drag & drop for simple request like find datas in a table for a dropdownlist, for exemple : Simple Values of the Civility table : Mr., Mrs. Architecture in this case : aspx <-> aspx.cs <-> SQL Server with a .xsd file But when i have more complex tables i use the architecture : aspx <-> aspx.cs <-> componentDb.cs <-> SQL Server where the componentDb file contain getter, setter and complex use of stored procedures.

" The other advantage of stored procedure is that it is precompiled and so it's faster. " Yes, stored procedures are usually precompiled (I called them "prepared", but mean the same thing). But when you use the EXEC on regular Sql text, teh sql text itself is not prepared, and defeats the purpose of using a precompiled stored procedure because the SQL text still has to be prepared. This depends on the database engine you use, I suppose. As far as my opinion, it sounds like you're asking for a comparison between the Visual drag'n'drop of ADO.NET components and ado.net programming through code only. Is that right? It is my opinion that it is easier to build database apps using drag and drop, but if your application has many database components, it becomes confusing. My coworkers have projects with 10 connections, 25 command objects, and 5 dataadapters. When you look at a project like this, you never know which component is what type, which is used and which is not, or which one is used where in the code. You also have to manage them using the property editor. This is nice for defining the properties, but it's lousy to look at see which objects work with other objects. When you do all this in code, you can easily look at a single block and see which connection is used, which command is used, and which dataadapter is used all at a glance. Since visual components are created by writing this very same code anyone (but in the designer-managed section), they are functionally the same. So, drag and drop components for small, simple projects. Use code-only for complex projects, or mix and match as you see fit. If you have 45 command objects but only one connection object, just create the connection object visually and the command objects in code.

Thanx for you answering... The other advantage of stored procedure is that it is precompiled and so it's faster. the most important question i'm asking me is if i make the good choice to developp with C# Components. In fact, we can create by a simple Drag & Drop on the "aspx file" an ADO.Net object (but we have no stored procedure associated. After you link the DataSet (for Example) to a Datagrid... It's really simple and fast to developp if we compare to Database Connexion with C# components (object programming). What's your choice between both ?

There are a few things to keep in mind. 1) Your SQL text may not be prepared properly when you use EXEC(querytext). It's usually just as good to use a query for this instead because it's just as fast. The only advantage to using a stored procedure instead of a query in this case is that it is more dynamic (which can also be bad). 2) If you specify the field name as a parameter, you should make sure that the program validates the field name and doesn't allow anything malicious to be passed. It would be wise to get the table schema before the input is accepted and do not permit anything to be passed unless it is a valid column name.

In fact, we can use the solution : Exec('Select * from Table where ' + @arg1 + '='' + @Val1 ''); but i think i will create more stored procedure how you said : getByName(name) getByAge(age) getByAgeName(name,age) ... for my search. Thanx

Hi, what you actually want is: Where Nom = 'Dupont', as Nom is the column name, and 'Dupont' is the student's last name. I would think that you would need to create a different stored procedure for each type of query that you would need. Eg: one for last name, one for first name, one for student number etc. Hope this helps