common genreal stored procedure in sql server
-
hello i want to write stored procedure in sql server 2000 , but i have a problem what should i do 1- i'll make a DAL (Data Access Layer) 2- i have more than 20 tables 3- if i want for example make the select query for article should i get all article and then make a function (procedure in vb.net) to get specified articles or should i make a query to get my article more precisely i want to get articles of a furnisher, 2 ways to do it : 1- make a stored procuder in sql server select * from article where fournisherid=123 2-get all article and make a function in vb.net select * from article dim l as list dim l1 as list // put data in l for i =0 to l.count if l.item(i).fournisherid=123 la.items.add(l.item(i)) next that's the idea for ure not the exact code so in the secode solution, i'm separating aplication from DB, but it's difficult to do in the first i need more that 100 procedure so what i can do !!!???? is there a stored procedure made, that can replace more than once using its parameters i don't know !!??? any one can help !???
-
hello i want to write stored procedure in sql server 2000 , but i have a problem what should i do 1- i'll make a DAL (Data Access Layer) 2- i have more than 20 tables 3- if i want for example make the select query for article should i get all article and then make a function (procedure in vb.net) to get specified articles or should i make a query to get my article more precisely i want to get articles of a furnisher, 2 ways to do it : 1- make a stored procuder in sql server select * from article where fournisherid=123 2-get all article and make a function in vb.net select * from article dim l as list dim l1 as list // put data in l for i =0 to l.count if l.item(i).fournisherid=123 la.items.add(l.item(i)) next that's the idea for ure not the exact code so in the secode solution, i'm separating aplication from DB, but it's difficult to do in the first i need more that 100 procedure so what i can do !!!???? is there a stored procedure made, that can replace more than once using its parameters i don't know !!??? any one can help !???
In general I would do most of the filtering on SQL Server. It is specifically designed for the task and it will save you lots of network bandwidth as you appear to be ignoring 99% of the data anyway.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
In general I would do most of the filtering on SQL Server. It is specifically designed for the task and it will save you lots of network bandwidth as you appear to be ignoring 99% of the data anyway.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
i see that u have right !! but should i write 100 stored procedure to cover all cases ... i have to take in consideration the order by , the group by , and the joins .... so i think more that 100 so !!!!! any solution ?
-
i see that u have right !! but should i write 100 stored procedure to cover all cases ... i have to take in consideration the order by , the group by , and the joins .... so i think more that 100 so !!!!! any solution ?
Lord Hasan wrote:
but should i write 100 stored procedure to cover all cases ...
What business processes are you dealing with? Surely that should dictate what data you need? Or are you writing a system that gives the user a lot of flexibility? When you say "all cases" is that all cases that you can think of, or all the cases the business requires?
Lord Hasan wrote:
i have to take in consideration the order by , the group by , and the joins ....
Again, what business processes do you have to deal with? Looking at the sytem I'm working on I can see there are over 300 stored procedures, so 100 is not so much by comparison. Remember you don't have to provide groupings and joins on every conceivable way to get at or manipulate the data, just the ones that have business value.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Lord Hasan wrote:
but should i write 100 stored procedure to cover all cases ...
What business processes are you dealing with? Surely that should dictate what data you need? Or are you writing a system that gives the user a lot of flexibility? When you say "all cases" is that all cases that you can think of, or all the cases the business requires?
Lord Hasan wrote:
i have to take in consideration the order by , the group by , and the joins ....
Again, what business processes do you have to deal with? Looking at the sytem I'm working on I can see there are over 300 stored procedures, so 100 is not so much by comparison. Remember you don't have to provide groupings and joins on every conceivable way to get at or manipulate the data, just the ones that have business value.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
i'm making a program to manage 1- stock, pieces , cars 2- reparation in garage 3- transportation for example : the table Articles: artcode (PK) Reference .. Price ... curent quantity commande quantity reserved quantity minimal quantity ... so if i will make select * where curentquantity =0 select * where commandequantity =0 select * where reservedquantity=0 select * where curentquantity < minimalquantity select * from article select * where artcode="XXXX" select * where reference="XXXX" select * where price=12323 select * where wheight=12323 ..... should i make all thes e procedures ???
-
i'm making a program to manage 1- stock, pieces , cars 2- reparation in garage 3- transportation for example : the table Articles: artcode (PK) Reference .. Price ... curent quantity commande quantity reserved quantity minimal quantity ... so if i will make select * where curentquantity =0 select * where commandequantity =0 select * where reservedquantity=0 select * where curentquantity < minimalquantity select * from article select * where artcode="XXXX" select * where reference="XXXX" select * where price=12323 select * where wheight=12323 ..... should i make all thes e procedures ???
Lord Hasan wrote:
select * where artcode="XXXX" select * where reference="XXXX" select * where price=12323 select * where wheight=12323
You can parameterise queries (especially stored procedures) so you create a stored procedure for
SELECT * FROM MyTable WHERE Reference = @Referencelike thisCREATE PROCEDURE dbo.GetForReference
@Reference VARCHAR(10)
AS
SELECT * FROM MyTable WHERE Reference = @Reference
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Lord Hasan wrote:
select * where artcode="XXXX" select * where reference="XXXX" select * where price=12323 select * where wheight=12323
You can parameterise queries (especially stored procedures) so you create a stored procedure for
SELECT * FROM MyTable WHERE Reference = @Referencelike thisCREATE PROCEDURE dbo.GetForReference
@Reference VARCHAR(10)
AS
SELECT * FROM MyTable WHERE Reference = @Reference
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
i'm making a program to manage 1- stock, pieces , cars 2- reparation in garage 3- transportation for example : the table Articles: artcode (PK) Reference .. Price ... curent quantity commande quantity reserved quantity minimal quantity ... so if i will make select * where curentquantity =0 select * where commandequantity =0 select * where reservedquantity=0 select * where curentquantity < minimalquantity select * from article select * where artcode="XXXX" select * where reference="XXXX" select * where price=12323 select * where wheight=12323 ..... should i make all thes e procedures ???
And another thing... You should define the column list in the SELECT statement for a number of reasons. Performance - Defining the list up front means SQL Server doesn't have to work it out. Reliability - Defining the list up front gives your stored procedure a consistent interface to the outside world. If you add or rearrange columns the stored procedure's output will remain the same - which means that the application using it will continue to work. If the changes are breaking changes then the point it breaks will be in the stored procedure which is closer to the change than anywhere else. This means it should be easy to find the dependencies and fix the remaining code.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
And another thing... You should define the column list in the SELECT statement for a number of reasons. Performance - Defining the list up front means SQL Server doesn't have to work it out. Reliability - Defining the list up front gives your stored procedure a consistent interface to the outside world. If you add or rearrange columns the stored procedure's output will remain the same - which means that the application using it will continue to work. If the changes are breaking changes then the point it breaks will be in the stored procedure which is closer to the change than anywhere else. This means it should be easy to find the dependencies and fix the remaining code.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
thanks man
-
Lord Hasan wrote:
so here i should make 4 procedure 4 these instructions
Well, it depends. Do you access them all together all the time. Or do you need to access them individually. When you display a customer order you will likely need to get a row from the orders table and several rows for each line item in the order. Rather than write two stored procedures, you can combine both SELECTs in to one stored procedure because you are always getting both bits of information at the same time.
Lord Hasan wrote:
so more than hundred procedure
Like I said, 100 stored procedures isn't that much. The system I'm currently working on has well over 300 stored procedures. I've worked on systems with less, and I've worked on systems with much more. When you write, say, C#, do you worry that you are writing thousands of methods? on dozens of classes? Probably, because there is no object orientation in the database and all stored procedures appear together does it seem more daunting. The key, I've found, is to have a good naming convention. That way you can find things easily and you will be able to work out what that stored procedure was you created last year but haven't needed to use since.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website