Stored Procedures that validates Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedures that validates Data

Author

Topic

Widz
Starting Member

11 Posts

Posted - 2009-10-20 : 09:22:52

I am trying to write a stored procedure that will first ask for an input of a number then validate it (<TABLE1>.AAA_NUMBER) (a number with 7 digits) and should produce an error if the number has more, less or charecters in it

If the number is valid it should be check for a <TABLE2>.DOCID from the second table, if that exist I wanted it to display it with the allocation date and if the <TABLE2>.DOCID does not exit I want an option to create one (using the increment).

There sequence of the numbers will depend on the year <TABLE3> coz different years will start with a different sequence

at the moment i have gone as far as just selecting the output as below

CREATE PROCEDURE [dbo].[Sp_Prescribing_Number]
SELECT AAA_NUMBER AS AAA_NUMBER
FROM <TABLE1>
ORDER BY AAA_NUMBER

ALTER PROCEDURE [dbo].[Sp_Presribing_Number] AS
SELECT
<TABLE1>.Surname,
<TABLE1>.Initials,
<TABLE1>.AAA_NUMBER,
<TABLE2>.DOCID,
CONVERT(VARCHAR (10),<TABLE2>.ALLOCATIONDATE,103) AS ALLOCATIONDATE,
<TABLE1>.Sex_Code
FROM <TABLE1>
LEFT JOIN <TABLE2> ON <TABLE2>.BBBNUMBER = <TABLE1>.AAA_NUMBER
WHERE <TABLE1>.DOCID IS NOT NULL

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-20 : 10:19:16

I'm not exactly sure what you're after here, but this might point in the right direction. This is given as an example only

Jim

declare @AAA_number varchar(10)

set @AAA_number= '123a567'

SELECT CASE WHEN len(@AAA_number) <> 7 or @AAA_number like '%[A-Z]%' THEN 'Not Valid' ELSE 'Valid' END

IF EXISTS(select 1 from <table2> t2 where t2.bbb_number = @AAA_NUMBER
BEGIN
SELECT
<TABLE1>.Surname,
<TABLE1>.Initials,
<TABLE1>.AAA_NUMBER,
<TABLE2>.DOCID,
CONVERT(VARCHAR (10),<TABLE2>.ALLOCATIONDATE,103) AS ALLOCATIONDATE,
<TABLE1>.Sex_Code
FROM <TABLE1>
LEFT JOIN <TABLE2> ON <TABLE2>.BBBNUMBER = <TABLE1>.AAA_NUMBER
WHERE <TABLE1>.DOCID IS NOT NULL

END

ELSE
<do something else>

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 12:44:44

can you post table structure with required output?

leah
Starting Member

2 Posts

Posted - 2009-10-20 : 14:26:39

Hi...I had a sql question...I want to do something in sql....like this...

I have a sql data source and I pass 1 parameters if id = chani but 2 parameters if id = malka and if id = malka then parameterA is for sure NOT NULL, soo I wanted to do something like this, but I think it's wrong...could you direct me maybe??

@parameterA
int ,
@parameterB
char(12),
AS
BEGIN
SET NOCOUNT ON;
IF @parameterA is not NULL --malka

BEGIN
SELECT * FROM
Malka
WHERE parameterA = @parameterA AND parameterB=@parameterB
ORDER BY enteredDate DESC
END
ELSE -- if chani

BEGIN
SELECT myname, * FROM chani
WHERE parameterB = @parameterB
END
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 14:41:30

please dont hijack threads...post as a new question

Widz
Starting Member

11 Posts

Posted - 2009-11-05 : 04:01:28

Thread Solved Thanks!! I have managed to get round it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources