|
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
|
|
|
|
|
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.
|
|
|
|
|
thanks alot
what about parameters if there are any can they just be placed in brackets
|
|
|
|
|
exec procname param1,param2,...
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
that was actually my first thought but i wasnt sure how to return multiple values from a function can this be done???
|
|
|
|
|
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.
|
|
|
|
|
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?????
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
