Database Discussion Boards

Re: Stored Procedures - Validating Variables

Mark Cabbage14-Sep-07 0:41

14-Sep-07 0:41

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

Re: Stored Procedures - Validating Variables

Pete O'Hanlon14-Sep-07 1:39

Pete O'Hanlon

14-Sep-07 1:39

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.

Re: Stored Procedures - Validating Variables

Michael Potter14-Sep-07 4:56

Michael Potter

14-Sep-07 4:56

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.

Re: Stored Procedures - Validating Variables

DerekFL14-Sep-07 9:14

DerekFL

14-Sep-07 9:14

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.

stored procedure replace function problem

soniasan13-Sep-07 1:08

soniasan

13-Sep-07 1:08

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'

Re: stored procedure replace function problem

Joe13-Sep-07 2:03

Joe

13-Sep-07 2:03

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

Re: stored procedure replace function problem

andyharman13-Sep-07 7:16

andyharman

13-Sep-07 7:16

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 + 1

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: ..

Pete O'Hanlon13-Sep-07 23:03

Pete O'Hanlon

13-Sep-07 23:03

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.

Re: ..

Joe14-Sep-07 0:29

Joe

14-Sep-07 0:29

Pete O`Hanlon wrote:
not keen on the blatant attempts to get MVP status

You got my 5 for your attitude Smile | :)

Re: ..

Pete O'Hanlon14-Sep-07 1:35

Pete O'Hanlon

14-Sep-07 1:35

Back at you Big Grin | :-D

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

SQL stored porcedures

OS GIKEN12-Sep-07 22:26

OS GIKEN

12-Sep-07 22:26

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 Cool | :cool:

This is what I think, so suck it up!

Re: SQL stored porcedures

Pete O'Hanlon12-Sep-07 22:54

Pete O'Hanlon

12-Sep-07 22:54

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.

Re: SQL stored porcedures

Frank Kerrigan13-Sep-07 6:11

Frank Kerrigan

13-Sep-07 6:11

Trying reading SQL Server books online (there are online at ms).

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Re: SQL stored porcedures

Paul Conrad14-Sep-07 19:20

Paul Conrad

14-Sep-07 19:20

OS GIKEN wrote:
UPDATE,DELETE,INSERT

Like the others have said, read up on these, and they are sql keywords, not stored procedures Smile | :)

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

Query not returning distinct value

miniThomas12-Sep-07 19:35

miniThomas

12-Sep-07 19:35

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

Re: Query not returning distinct value

ritu432112-Sep-07 20:13

ritu4321

12-Sep-07 20:13

there is nothing as trim in sql query. Try removing trim you will get the result.

Re: Query not returning distinct value

/randz12-Sep-07 23:25

/randz

12-Sep-07 23:25

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.

Re: Query not returning distinct value

Michael Potter13-Sep-07 4:46

Michael Potter

13-Sep-07 4:46

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

Re: Query not returning distinct value

Frank Kerrigan13-Sep-07 6:15

Frank Kerrigan

13-Sep-07 6:15

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

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Trying to extract data from a single table

Skanless12-Sep-07 8:18

Skanless

12-Sep-07 8:18

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

Re: Trying to extract data from a single table

andyharman13-Sep-07 2:32

andyharman

13-Sep-07 2:32

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 CreateDate

The "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".

Re: Trying to extract data from a single table

Skanless13-Sep-07 5:46

Skanless

13-Sep-07 5:46

Thanks for the your help. It sort does what I want however because a message that was created 1/5/2005 may not be resolved until sometime in March its current Status will not be 'Resolved' thus will not fall within this status period. Looking at the code that messages status may be counted during a January monthly report the status is not resolved only becuase the message was created in January. I guess I did not provide information but I am trying to extract and count ALL messages created during the period and all messages Resolved during the period. Agian, a messages created during that period may not be resolved until a future date hence we do not need to count their status on their status is 'Resolved' thus keying off the statusDate.

Thanks for you response. I like the way you clean up the code.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

Re: Trying to extract data from a single table

Skanless13-Sep-07 5:54

Skanless

13-Sep-07 5:54

A simple 'OR' Statement should work.

SELECT dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS Period, COUNT(*) AS IssuesReceived, SUM(dbo.RPT_IsInquiryCompleted(CurrentStatusID)=1) AS IssuesClosed FROM Ms_threadsWHERE CreateDate BETWEEN @BeginningDate and @EndDateGROUP
or StatusDate BETWEEN @BeginningDate and @EndDate(This would have to be union with CreateDate for it to work.)
GROUP BY CreateDate
ORDER BY CreateDate

unfortunately the OR statement as scripted is not working either.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

Re: Trying to extract data from a single table

andyharman13-Sep-07 6:45

andyharman

13-Sep-07 6:45

Hi Skan

How about:

SELECT dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) AS Period,
    SUM(A.IssueReceived) AS IssuesReceived,
    SUM(A.IssueClosed) AS IssuesClosed
FROM (
  SELECT CreateDate AS PeriodDate,
      1 AS IssueReceived,
      0 AS IssueClosed
    FROM Ms_threads
    WHERE CreateDate BETWEEN @BeginningDate and @EndDate
  UNION ALL
  SELECT StatusDate AS PeriodDate,
      0 AS IssueReceived,
      dbo.RPT_IsInquiryCompleted(CurrentStatusID) AS IssueClosed
    FROM Ms_threads
    WHERE StatusDate BETWEEN @BeginningDate and @EndDate
) A
GROUP BY A.PeriodDate
ORDER BY A.PeriodDate

The select statements within the from-clause count the issues openned and closed during the selected period. The outside select statement then groups everything together.

The technique is fairly common. If you have problems understanding what it is doing then run each of the select statements individually.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: Trying to extract data from a single table

Skanless13-Sep-07 8:00

Skanless

13-Sep-07 8:00

Dude you are a Genius. I modified the script a but and it worked like a baby. Thnks dude.. I owe u a beer.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_MS_IssuesReport_Num2](@PeriodName varchar(50), @BeginningDate datetime, @EndDate datetime)AS
BEGIN

SELECT dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) AS Period,
SUM(A.IssueReceived) AS IssuesReceived,
SUM(A.IssueClosed) AS IssuesClosed
FROM(
SELECT CreateDate AS PeriodDate,
1 AS IssueReceived,
0 AS IssueClosed
FROM Ms_threads
WHERE CreateDate BETWEEN @BeginningDate and @EndDate
UNION ALL
SELECT StatusDate AS PeriodDate,
0 AS IssueReceived,
dbo.RPT_IsInquiryCompleted(CurrentStatusID) AS IssueClosed
FROM Ms_threads
WHERE StatusDate BETWEEN @BeginningDate and @EndDate
) A
GROUP BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) -- Added the date floor function.
ORDER BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate)
end

Thank Code Project....This deserves a 10 not a 5...hehehehe!!

Skan

If you knew it would not compile why didn't you tell me?!?!?!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.