sql stored proc - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

sql stored proc

Author

Topic

behenkaloda
Starting Member

1 Post

Posted - 2012-01-20 : 19:03:11

Hi guyz,
I am stuck and dont know how to do this.

I need a stored procedure to take the following input:

@input varchar(50) = ‘210,0,10,3100’
@AilmentID INT = 1234

Parse input into individual values:
210
0
10
3100

Look up the MeasurementID for those values which are stored in the “abbreviation” column of the Measurement table.

The first value in the list is a specific type of data with DataPointID = 69
2nd value DataPointID = 70
3rd value DataPointID = 76
4th value DataPointID = 74

Now I need to check and see if there is a record in the AilmentAssay table with the supplied AilmentID and DataPointID.

If yes, then update that value to the new MeasurementID just looked up.

If no, insert a row into the AilmentAssay table with the new AilmentId, DatapointID and MeasurementID

Here are the tables and data:

CREATE TABLE [dbo].[AilmentAssay](
[AilmentAssayID] [int] IDENTITY(0,1) NOT NULL,
[AilmentID] [int] NOT NULL,
[DataPointID] [int] NOT NULL,
[MeasurementID] [int] NOT NULL)

GO

CREATE TABLE [dbo].[Measurement](
[MeasurementID] [int] IDENTITY(0,1) NOT NULL,
[Description1] [varchar](200) NOT NULL,
[Abbreviation] [varchar](50) NOT NULL)

INSERT INTO dbo.Measurement (Description1 ,Abbreviation)
VALUES ('1+, 20%', '120'),('2+, 50%', '250'),
('15 - 20%','15-20'),('10%','10'),('3+, 100%', '3100'),
('0%', '0'),('2+, <10%','2<10')

CREATE TABLE [dbo].[MeasurementTypeAssoc](
[MeasurementTypeAssocID] [int] IDENTITY(1,1) NOT NULL,
[MeasurementID] [int] NOT NULL,
[CodeTypeID] [int] NOT NULL)
INSERT INTO dbo.MeasurementTypeAssoc( MeasurementID ,CodeTypeID)
VALUES ( 1,188),(2,188), (3,188),(4,188),(5,188),(6,188),(7,188)

The MeasurementTypeAssoc table pulls the records from the Measurement table that are candidates for this lookup.

-- thanks, if u can help me

REGARDS,
Behenkaloda

biswajitdas
Starting Member

44 Posts

Posted - 2012-01-22 : 18:43:49

you need to use the CASE structure to update the column depend upon the multiole value.
Please check the below link have lot of example

http://www.craigsmullins.com/ssu_0899.htm

Sr Sql server DBA/Artitech

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources