|
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.
|
|
|
|
|
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 = @Reference
like this
CREATE PROCEDURE dbo.GetForReference
@Reference VARCHAR(10)
AS
SELECT * FROM MyTable WHERE Reference = @Reference
|
|
|
|
|
select * where artcode=@artcode
select * where reference=@ref
select * where price=@price
select * where wheight=@w
so here i should make 4 procedure 4 these instructions
so more than hundred procedure:S
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
|
Hi all,
I am having a stored procedure which requests 2 parameres for the where statement. i wanna fill a dataset from this stored procedure and then create a crystal report from that.
dataset fill happenes when user clicks
please help me and tell me a way to do thid.
Regards
Ruwandi
rkherath
|
|
|
|
|
Design A report(suppose its ID is myReport)
after populating your dataset(say myDataset) try this code.....
[take a CrystalReportViewer Control(say it's ID is myViewer)]<br />
Dim oRpt As New myReport<br />
oRpt.SetDataSource(myDataset)<br />
myViewer.ReportSource = oRpt
Tirtha
Miles to go before I sleep
|
|
|
|
|
Hi,
Thanks. but i wanna keep a certain format in the report . can i do that with this.
eg:
if dataset fills like
group groupNum Code balance
A 1 a1 10
A 1 b1 15
B 2 a2 20
B 2 b2 25
C 3 a3 30
C 3 b3 12
but i need report to display
code balance
a1 10
b1 15
A 1 25
a2 20
b2 25
B 2 45
a3 30
b3 12
C 3 42
total 112
i hope you can understand
Regards
ruwandi
regards
ruwandi
rkherath
|
|
|
|
|
You can Group your data on your "group" field....and for the addition of balance amount you can use a Simple Formula in Crystal report....
Tirtha
Miles to go before I sleep
|
|
|
|
|
thx but how about displaying group number infront of the gruop
regards
Ruwandi
rkherath
|
|
|
|
|
|
Concatenate the array values into a string with some Separator or Delimiter(e.g comma,apostrophe,tilde etc.) and insert in table as Text....
Tirtha
Miles to go before I sleep
|
|
|
|
|
the array is in vb.net ????
if yes : so u can place it in a datatable
and then insert it in table of the data base
|
|
|
|
|
Please will someone advise me on a resource that nicely explains security using Management Studio. I need to grant a permission to the database owner, but dbo isn't a real user, so how can I grant the permission? The user is linked to login 'sa'.
|
|
|
|
|
Brady Kelly wrote: The user is linked to login 'sa'.
If the user is "sa" surely they have permission to do anything?
|
|
|
|
|
The term 'surely' is the most innaproprate term I have ever come across for describing anything related to this area of SQL Server security.
|
|
|
|
|
Okay - "sa" has permission to do anything it likes.
|
|
|
|
|
Except create unsafe assemblies. I had to log in under my Windows account and grant that user the unsafe assembly permission.
|
|
|
|
|
Ah... I'm still going on my knowledge of SQL Server 2000. My bad.
|
|
|
|
|
Hi
I have a table with datetime field and a stored procedure which should update this table on the date n time specified. This should take place automaticaly at the specified date and time.
Kindly suggest any solutions to this query.
Thanking you in anticipation.
Regards
Siddhi
|
|
|
|
|
Helllo All,
I want to keep my database secure by backened. Actually i donot want to let anybody to make any modification in my database(Sql Server2000) through Enterprises Manager and also only the authorised users can have accesss to the database through frontened(ASP.NET)
Plz.......................
..........................
|
|
|
|
|
The Knowledge wrote: Actually i donot want to let anybody to make any modification in my database(Sql Server2000) through Enterprises Manager
Not gonna happen.
Enterprise manager can connect to the database using any account that has been set up. (Typically it will connect as sa [the sys admin]). If you have any kind of access to the database from an application, Enterprise Manager will be able to connect using the same credentials.
|
|
|
|
|
I'm busy musing over the best way to deploy a CLR stored procedure. Where do I put it before pulling the assembly into the database? Do I use a normal setup routine to an install folder and then invoke my deployment scripts using the code-base in that folder?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
