Database

Re: Help determining start and end times associated with today's date

26-Apr-07 4:41

Well, assuming that there is something unique that links the start and end tasks together, you could use:

SELECT O.[ID], O1.[ID] As ID2
FROM 
tblOne O
INNER JOIN tblOne O1
ON O.SomeUniqueKey = O1.SomeUniqueKey AND
O1.EventType = 'END' AND O1.Date >= @Today
WHERE 
O.EventType = 'START AND O.Date <= @Today

Deja View - the feeling that you've seen this post before.

Marcus J. Smith26-Apr-07 5:39

Marcus J. Smith

26-Apr-07 5:39

Thanks!

Stored Procs in Stored Procs

tadhg8826-Apr-07 3:39

26-Apr-07 3:39

hi i am just wondering if u can use a stored proc(to have a group of values returned) in a stored proc or an sql statement and if so how to go about doin it thanks in advance
Tim

Pete O'Hanlon26-Apr-07 3:52

26-Apr-07 3:52

In SQL Server, you would execute it inside your stored procedure using EXEC procname.

Deja View - the feeling that you've seen this post before.

tadhg8826-Apr-07 4:20

26-Apr-07 4:20

thanks alot
what about parameters if there are any can they just be placed in brackets

Hasan Jaffal26-Apr-07 4:22

26-Apr-07 4:22

exec procname param1,param2,...

tadhg8826-Apr-07 4:45

26-Apr-07 4:45

thanks again but one last q about this statement i want to execute the proc pr_cd_GetFirstTeamLeagues which accepts a clubid which is 7 here do u have any idea why it aint workin

Select s.ID
From Match m
Join Squad s on (m.SquadA = s.ID or m.SquadB = s.ID)

Where exec pr_cd_GetFirstTeamLeagues 7

And m.CompetitionID = 0 -- Premier League
And m.Date Between (Select StartDate From Seasons Where ID = 50) And (Select EndDate From Seasons Where ID = 50)
Group By s.ID
Order By dbo.pr_bll_GetSquadLeaguePointsForSeason(s.id, 50) Desc

Pete O'Hanlon26-Apr-07 4:48

26-Apr-07 4:48

If you are wanting to do something like this, then you must use a stored function rather than a stored procedure.

Deja View - the feeling that you've seen this post before.

tadhg8826-Apr-07 4:53

26-Apr-07 4:53

that was actually my first thought but i wasnt sure how to return multiple values from a function can this be done???

Pete O'Hanlon26-Apr-07 4:58

26-Apr-07 4:58

It depends on what you mean by multiple values. Inherently, no it can't - a function returns one value. However, this value might be an in memory table so it is possible to return multiple items.

Deja View - the feeling that you've seen this post before.

tadhg8826-Apr-07 5:27

Paging in SQL Server per-2005

26-Apr-07 5:27

ok im just gonna explain what im tryin to get done which i prob shud have done ages ago
i basically want to be able to reduce the squads ids im workin with to the top 6 only so im tryin to use something like this

where SquadID in dbo.GetTopSixInLeague()

but im gettin a syntax error near 'dbo.'
so im guessing this is the wrong way to go about doin this?????

__DanC__26-Apr-07 1:49

__DanC__

26-Apr-07 1:49

Hi,

Is there a way to page results in SQL Server pre-2005? I know about the ROW_NUMBER() and OVER method but that only works in 2005.

I have tried a few methods using 'id < x' and 'id > x' but they seem to be a bit fiddly. Just wondering if there's a better method.

Thanks in advance.

Dan

Chat | Text Messaging | Games | www.uzeddit.com - Coming soon!

common genreal stored procedure in sql server

Hasan Jaffal26-Apr-07 0:12

26-Apr-07 0:12

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 !???

Colin Angus Mackay26-Apr-07 1:02

26-Apr-07 1:02

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

Hasan Jaffal26-Apr-07 1:28

26-Apr-07 1:28

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 ?

Colin Angus Mackay26-Apr-07 2:19

26-Apr-07 2:19

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.

Hasan Jaffal26-Apr-07 3:16

26-Apr-07 3:16

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 ???

Colin Angus Mackay26-Apr-07 4:14

26-Apr-07 4:14

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

Hasan Jaffal26-Apr-07 4:21

26-Apr-07 4:21

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

Colin Angus Mackay26-Apr-07 5:18

26-Apr-07 5:18

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.

Colin Angus Mackay26-Apr-07 4:21

26-Apr-07 4:21

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.

Hasan Jaffal26-Apr-07 4:31