|
Hi all,
Let me supply the Code and then explain:
DECLARE @vchRequestNumber Integer<br />
<br />
SELECT @vchRequestNumber = MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0))))+1<br />
FROM tblRequest
Ok, here's the deal..
What works:
IF vchRequestNumber is GREATER THAN 0 in the TABLE, the code WORKS.. and therefore I am presented with a value of vchRequestNumber PLUS 1.
What Doesn't work:
IF vchRequestNumber is NULL, then a ZERO replaces it(which is fine); BUT it is returning 0 instead of 0 PLUS 1.
Anyone has any ideas???
|
|
|
|
|
Hi Dayekh,
try this:
DECLARE @vchRequestNumber INT<br />
<br />
SELECT @vchRequestNumber = MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0))))<br />
FROM tblRequest<br />
<br />
SET @vchRequestNumber = @vchRequestNumber + 1
Greetings
Ralph
|
|
|
|
|
hehehe.. i tried that exact thing right now.. but it doesn't work...
I found the problem tho.. when no records exist, i.e. when vchRequestNumber is NULL, the ENTIRE evaluation is returning a NULL... so this is what I had to do:
ISNULL(MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0)))),0)+1
Thank you for the reply!
|
|
|
|
|
Alternatively, you could use COALESCE(vchRequestNumber, 0) + 1
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I currently have a SQL statement that basically looks like this.
SELECT [ID]
FROM
tblOne O
WHERE
(O.EventType = 'START'
AND O.Date <= @Today)
OR (O.EventType = 'END'
AND O.Date >= @Today) What I need to do is to pull records that have both a START and END that fit the criteria and if one is missing I need to exclude it. Is that possible through a SQL statement or will I need to continue looping through the records as this code already does?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Hi,
replace your "OR" with "AND"
Greetings
Ralph
|
|
|
|
|
Unfortunately, that won't work in the example because the columns are the same for both parts.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Which is why this sucks.
Im just trying to avoid getting the records back and then looking through them to determine what needs to be present on my side.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
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.
|
|
|
|
|
Thanks!
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
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 ???
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
