|
|
Hi Everyone.
I'm finding it hard to get information of verifying variables passed into a stored procedure. Specifically if the value is NULL then I want to abort the procedure and let VB.NET know the insert failed without issuing an exception.
I found some information that gave me the following IF statement but the layout is not like VB so I'm not sure where to go from here...
IF (@RateLevel1 IS NOT NULL) OR (@RateLevel2 IS NOT NULL)
NOTE: I borrowed the following code from a tutorial video so if you find any issues please let me know.
ALTER PROCEDURE dbo.sptblBillingRatesInsert
(
@Description nvarchar(150),
@RateLevel1 money,
@RateLevel2 money,
@RateLevel3 money,
@RateLevel4 money,
@RateLevel5 money,
@BillingLevelID int OUTPUT,
@ModifiedStamp timestamp OUTPUT
)
AS
SET NOCOUNT ON;
-- Inserts a row in Billing Rates
INSERT INTO tblBillingRates
(Description, RateLevel1, RateLevel2, RateLevel3, RateLevel4, RateLevel5)
VALUES
(@Description, @RateLevel1, @RateLevel2, @RateLevel3, @RateLevel4, @RateLevel5);
-- Checks if the last statement produced an error
IF @@ROWCOUNT>0 AND @@ERROR=0
SELECT @BillingLevelID = BillingLevelID, @ModifiedStamp = ModifiedStamp
FROM tblBillingRates
WHERE (BillingLevelID = SCOPE_IDENTITY());
|
|
|
|
|
hi,like this?
IF (@RateLevel1 IS NOT NULL) and (@RateLevel2 IS NOT NULL) ...
begin
raiserror 'parameters null value!'
return 1 --fail
end
else
begin
insert into ...
return 0 --success
end
|
|
|
|
|
Why do you want to do this? If you have designed your system properly, then you should have code in place at a much higher level to prevent this from happening. It is much better to warn the user that there is a problem and give them a chance to do something about it before you attempt the insert, which would just be an unnecessary trip to the database.
In general, it is much better to validate input as close to the user as possible.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi Pete,
I was watching the Microsoft VB training videos by Beth Massi. She showed two things:
- using stored procedures server side to make the app more secure
- using VB code on the dataset to check data and issue error providor status for that column.
These items are supposed to keep the "business logic" away from the forms and in a central location rather than re-keying in different forms.
Well I put it into practice and it works nicely, BUT ONLY IF you type the right things in....but as we all know users rarely do as they should. I can break the system easily if I tab through all fields in the datagrid. The error provider flags them as needing attention (NULL ETC) but once you reach that last field it causes an unhandled exception. My instinct is to handle the excpetion on the form using an event like leavecell or dataerror (not sure on names sorry) BUT then I'm splitting my logic between the dataset, stored procedure and the form....and breaking the rules of centralising....
Hence I thought maybe I should look to do it in the stored procedure....but as you say that means the user doesn't get a mice message and warning......so confused
|
|
|
|
|
A "typical" design would be to have a user interface where you would identify which elements of the data absolutely had to be present and validate that they were there before you could do anything else. Once the data has been validated, it is then passed to a business layer. Again, the business layer would be responsible for validating the data. Finally, the data would be passed over to the data layer.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I validate at both locations. This is especially necessary when you code in a team. The DBA is responsible for making sure, to the best of his/her ability, that garbage is not put into the system and that the user/developer knows why there is a rejection of input.
The front end developer should make sure that they limit the amount of rejections to the best of their ability. I find that checking inputs on both sides is highly beneficial and reduces DB errors dramatically.
The only time I stray from this is when the data needs to be verified against other existing data in the database. Here I make the DBA sole responsibility so that verfication data does not need to go back and forth across the wire.
|
|
|
|
|
The way you have it does not allow nulls so you may just want to handle the exception msg 201 as a null parameter. The other way is to add a default @RateLevel1 money = NULL and have an output parameter @IsNull of type bit and set to true if any parameters are null.
|
|
|
|
|
update @lia_table1<br />
set vou_id = replace((select voucher_id from confirm_detail where <br />
bkng_no in (select bkng_no from @lia_table1 where ids = @minsum + 1)<br />
and voucher_no in (select max(voucher_no) from confirm_detail where <br />
bkng_no in (select bkng_no from @lia_table1 where ids = @minsum + 1) group by bkng_no))<br />
where ids = @minsum + 1,'con','dom')
This is giving me error near where .
actually i want this replace the voucher_id 'con_ccg_199' with 'Dom_ccg_198'
|
|
|
|
|
soniasan wrote: This is giving me error near where .
I guess your where clause(where ids = @minsum + 1) is outside the outerquery
<br />
(select voucher_id from confirm_detail where .....)
If it needs to be along with the outerquery, you might be in need of AND clause instead of WHERE.
Also, looks to me like the query is muddled up with respect to the inner query's termination.
Probably executing the inner-queries individually might help you analyse the query in a better way.
HTH
|
|
|
|
|
If you're using SQL-Server then I think that the following may work for you:
UPDATE A SET
vou_id = REPLACE(B.voucher_id, 'con', 'dom')
FROM @lia_table1 A
INNER JOIN confirm_detail B
ON B.bkng_no = A.bkng_no
AND B.voucher_no = (
SELECT MAX(voucher_no) FROM confirm_detail C
WHERE C.bkng_no = A.bkng_no)
WHERE A.ids = @minsum + 1Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
I don't - I'm not keen on the blatant attempts to get MVP status.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O`Hanlon wrote: not keen on the blatant attempts to get MVP status
You got my 5 for your attitude 
|
|
|
|
|
Back at you
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi, can anyone help me understand stored procedures? UPDATE,DELETE,INSERT,GET. I need to understand it, not just copy it and do my work, I want to understand it, Im using SQL server 2005
Thanks
This is what I think, so suck it up!
|
|
|
|
|
OS GIKEN wrote: UPDATE,DELETE,INSERT,GET
Those aren't stored procedures. They relate to the way that you manipulate data in the database. They are typically referred to as CRUD operations (or Create/Retrieve/Update/Delete) - and it's not GET, it's SELECT.
A stored procedure is a mechanism by which code is stored in a database server, which can then be executed at a later stage. The code is written in the particular dialect that the database engine can "compile". For instance, in SQL Server, the stored procedure is written in T/SQL (Transact SQL). I hope that this helps to give you a start.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Trying reading SQL Server books online (there are online at ms).
|
|
|
|
|
OS GIKEN wrote: UPDATE,DELETE,INSERT
Like the others have said, read up on these, and they are sql keywords, not stored procedures 
http://www.w3schools.com/sql/[^] might help you some...
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
I am firing a query as "select distinct(trim(opp_name)) from tblOpps"
but this query returns
============
Renewal
Services
Renewal
New
I want Renewal to appear only once not twice
Thnaks,
Mini
|
|
|
|
|
there is nothing as trim in sql query. Try removing trim you will get the result.
|
|
|
|
|
i agree with ritu4321. but if you intend to remove trailing or preceding space, you can use rtrim or ltrim. But please note that ltrim and rtrim does not remove extra spaces within the string, only those at the beginning (for ltrim) and those at the end (for rtrim). Hope this helps.
Remember, your work is not yours alone. Somewhere, there are some codes written by others amongst us that depends on your work. By failing to see that you are part of their ecosystem, you are bound to break their code.
|
|
|
|
|
DISTINCT should be used as a SELECT operator, not as a function. I also do not know where you get the trim() function from.
SELECT DISTINCT
LTRIM(RTRIM(opp_name))
FROM
tblOpps
|
|
|
|
|
if you get stuck try and use the len (length function) sometimes using or importing unicode string (nvarchar etc) can cause issues.
<br />
<br />
SELECT DISTINCT <br />
LTRIM(RTRIM(opp_name)),<br />
len(opp_name),<br />
len(LTRIM(RTRIM(opp_name)))<br />
FROM tblOpps<br />
<br />
|
|
|
|
|
I attempting to extract data from a single table based on the two different criteria then presenting the information as one table. In the first instance I am trying to extact the date and count() of all messages received with a given time period(the query is based on createdDate). In the second intsatnce I am attempting to extrtact data based on it current status(i.e resolved, resaerched, open) I have created two temp table that provides me with the data I need now I need to group them by date so I can display the data by months for example:
Beginning Period Received Messages Resolved Issues
1/1/2007 15 6
2/1/07 8 19
3/1/07 25 10
And so forth. I have inclused the script any assistance will be great appreciated.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_MS_IssuesReport](@PeriodName varchar(50), @BeginningDate datetime, @EndDate datetime)AS
BEGIN
DECLARE
@Period datetime,
@MessagesReceived bigint,
@MessagesClosed bigint
SET NOCOUNT ON;
CREATE TABLE #MS_MessagesReceived(
DateCreated datetime,
IssuesReceived int,
)
CREATE TABLE #MS_MessagesStatus(
DateCreated datetime,
IssuesClosed int
)
CREATE TABLE #MS_MessagesReceivedAndStatuses(
DateCreated datetime,
IssuesReceived int,
IssuesClosed int
)
Insert Into #MS_MessagesReceived(DateCreated,IssuesReceived)
Select dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS 'Period', count(CurrentStatusID) from Ms_threads
where CreateDate BETWEEN @BeginningDate and @EndDate
Group by CreateDate,CurrentStatusID
Order by CreateDate
Insert Into #MS_MessagesStatus(DateCreated, IssuesClosed)
Select dbo.RPT_GetPeriod(@PeriodName, StatusDate) AS 'Period',count(CurrentStatusID)from Ms_threads
where dbo.RPT_IsInquiryCompleted(CurrentStatusID) = 1
and StatusDate BETWEEN @BeginningDate and @EndDate
Group by StatusDate, CurrentStatusID
Order by StatusDate, CurrentStatusID
SELECT @MessagesReceived = count(IssuesReceived) from #MS_MessagesReceived
SELECT @MessagesClosed = count (IssuesClosed) from #MS_MessagesStatus
SELECT @Period =dbo.RPT_GetPeriod(@PeriodName, DateCreated) from #MS_MessagesReceived
INSERT INTO #MS_MessagesReceivedAndStatuses(DateCreated, IssuesReceived, IssuesClosed)
VALUES(@Period, @MessagesReceived, @MessagesClosed)
Select DateCreated 'Time Period Beginning' , IssuesReceived 'Issue Received', IssuesClosed 'Issue Closed'
from #MS_MessagesReceivedAndStatuses
Drop Table #MS_MessagesReceived
Drop Table #MS_MessagesStatus
Drop Table #MS_MessagesReceivedAndStatuses
END
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
How about:
SELECT dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS Period,
COUNT(*) AS IssuesReceived,
SUM(dbo.RPT_IsInquiryCompleted(CurrentStatusID)) AS IssuesClosed
FROM Ms_threads
WHERE CreateDate BETWEEN @BeginningDate and @EndDate
GROUP BY CreateDate
ORDER BY CreateDateThe "SUM" expression assumes that your function returns either 0 or 1.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
