Need some guidance on how to approach a request for a stored procedure

Question

Need some guidance on how to approach a request for a stored procedure

Leeland

SSCrazy Eights

Points: 8959
More actions
September 8, 2006 at 11:49 am

#114326

I was asked to write a SP for a developer with the following logic

So here is the table layout
MatNum   VerNumMat   MatDesc                                          SpecSta
----------- ----------- --------------------------------------        -------
199         1                  100 ga Allied 2500 biax nylon film           I
199         2                  100 ga Allied 2500 biax nylon film           I
199         3                  100 ga Allied 2500 BON film                    I
199         4                  100 ga Allied 2500 BON film                    I
199         5                  100 ga Allied 2500 BON film                    I
199         6                  100 ga Allied 2500 BON film                    I
199         7                  100 ga 2500 BON film                             A
The MatNum & VerNumMat are the Pk
VerNumMat is the version of the record...as you could probably already guess the SpecSta is the status.
I = Inactive
A = Active
H = Hold
D = Development

The developer wants to be able to query the table and return the MatNum, MatDesc based on the following logic

IF the SpecSta is an A or D Return SpecSta, VerNumMat, MatNum
Elseif SpecSta is an H Return SpecSta, VerNumMat, MatNum
Elseif SpecSta is an I Return MAX REVISION SpecSta, VerNumMat, MatNum
ELSE Nothing

My Question
How do I go about pulling a query that gets me a list of just the MatNums (because there are multiples in the table) to use as the select criteria for the logic.
Or put another way, I think I need to run a subquery to obtain a list of distinct MatNum so I can then select against the table to pull a subset of information
and then run the logic against that subset of information to return some values.

Any advice is appreciated

Leeland

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I am not sure what you mean by MAX REVISION. This sounds like a pretty simply query; to that end, I may have misunderstood just precisely what you are looking for...

Here is some sample data including yours and a stored procedure which will return data dependent upon the input...

IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Product') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE dbo.Product

GO

CREATE TABLE dbo.Product( MatNum integer,

VerNumMat integer,

MatDesc varchar(35),

SpecSta char(1))

INSERT INTO dbo.Product

SELECT 199, 1, '100 ga Allied 2500 biax nylon film', 'I' UNION ALL

SELECT 199, 2, '100 ga Allied 2500 biax nylon film', 'I' UNION ALL

SELECT 199, 3, '100 ga Allied 2500 BON film', 'I' UNION ALL

SELECT 199, 4, '100 ga Allied 2500 BON film', 'I' UNION ALL

SELECT 199, 5, '100 ga Allied 2500 BON film', 'I' UNION ALL

SELECT 199, 6, '100 ga Allied 2500 BON film', 'I' UNION ALL

SELECT 199, 7, '100 ga 2500 BON film', 'A' UNION ALL

SELECT 200, 1, '200 ga Allied 2500 biax nylon film', 'H' UNION ALL

SELECT 200, 2, '200 ga Allied 2500 biax nylon film', 'H' UNION ALL

SELECT 200, 3, '200 ga Allied 2500 BON film', 'D' UNION ALL

SELECT 200, 4, '200 ga Allied 2500 BON film', 'D' UNION ALL

SELECT 200, 5, '200 ga Allied 2500 BON film', 'I' UNION ALL

SELECT 200, 6, '200 ga Allied 2500 BON film', 'A' UNION ALL

SELECT 200, 7, '200 ga 2500 BON film', 'A'

--SELECT * FROM dbo.Product

IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetProducts') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE dbo.GetProducts

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROC dbo.GetProducts

@SpecSta char(1)

AS

SELECT CASE

WHEN SpecSta = 'I'

THEN 'Inactive'

WHEN SpecSta = 'A'

THEN 'Active'

WHEN SpecSta = 'H'

THEN 'Hold'

WHEN SpecSta = 'D'

THEN 'Development'

-- ELSE ' ' -- It does not look as if you will need this...

END AS SpecSta,

VerNumMat, MatNum

FROM dbo.Product

WHERE @SpecSta = SpecSta

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

---------------------------------------------------------------------------------

EXECUTE dbo.GetProducts 'I'

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

This may be the logic Lee's looking for...

SELECT MAX(VerNumMat) AS Revision, 'I' AS SpecSta, a.MatNum 
FROM Product a
JOIN(SELECT DISTINCT MatNum FROM Product WHERE SpecSta = 'I')b
ON a.MatNum = b.MatNum
WHEREa.SpecSta = 'I'
GROUP BYa.MatNum

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Hi buddy!

Either you're right or you're SCC razy...

I wasn't born stupid - I had to study.

Leeland SSCrazy Eights Points: 8959 More actions · Answer 4

Well thanks for the posts guys. I got more information finally from my supervisor and he showed me a different direction basically with nested If statements and a temp table.

The goal changed as well from the original story I got. This information is going to be used to feed into a different process. SO...the stored procedure will run against a table, and have two portions.

The first portion will check to make sure the information isn't in the temp table, if it isn't it will grab the matnum, desc and status and enter it in the table.

The second process will check for the revision number and then do an update to the row thus getting the basic results.

I had never thought about using a staging\temp table or anything like that...but then again I was never really told the scope of the request either...darn developers

Thanks again guys...maybe once I get it written I will post the code to better explain what I was talking about.

Lee