Stored Procedure & Filtering (With Picture)

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)

Stored Procedure & Filtering (With Picture)

Author

Topic

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-08 : 05:08:49

Can it possible that combine more than 1 stored procedure (SP)?

hope that can show me some coding as well. Just simple also can
thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 05:12:56

What do you mean by combining Tables? Union?

select col1, col2, col2, ... from tbl1
union all
select col1, col2, col2, ... from tbl2

I am not sure what you mean by combining SPs but you can create one driver SP which will call two or more SPs or you can nest SPs by calling one from another.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-08 : 09:15:35

okay, to let it more clear, i have attach picture as below:

i have 3 table like this,

so i want the result be like this

**the 3 table i generate i was using the SP to create it, so i hope that my result table must use back the store procedure to combine...

Hope can hear some expert guide me.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 09:22:47

select table2.joborderno, isnull(table1.productname, '-') as productname, isnull(convert(varchar, table1.duedate, 3), '-') as duedate, isnull(convert(varchar, table1.schedulecompletedate, 3), '-') as schedulecompletedate, isnull(table2.orderqty, 0) as orderqty, isnull(table3.[qty in progress], 0) as [qty in progress]
from table2
left join table1 on table1.joborderno = table2.joborderno
left join table3 on table3.joborderno = table2.joborderno
order by table2.joborderno

(Table1-Table3 is from above, top to bottom)

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-08 : 10:00:28

actually the 3 table above, i separate it by using Stored Procedure.

so, how i can write it be another stored procedure like you show me like this?

select table2.joborderno, isnull(table1.productname, '-') as productname, isnull(convert(varchar, table1.duedate, 3), '-') as duedate, isnull(convert(varchar, table1.schedulecompletedate, 3), '-') as schedulecompletedate, isnull(table2.orderqty, 0) as orderqty, isnull(table3.[qty in progress], 0) as [qty in progress]
from table2
left join table1 on table1.joborderno = table2.joborderno
left join table3 on table3.joborderno = table2.joborderno
order by table2.joborderno

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-08 : 10:12:47

here is how i use stored procedure and separate the 3 different table

1st table
SELECT PartProgID AS [JobOrderNo], ProductName, DueDate, ScheduledCompleteDate
FROM WTLOT
GROUP BY PartProgID, ProductName, DueDate, ScheduledCompleteDate

2nd table
SELECT JobOrderNo, OrderQty
FROM EXTERNAL_SO

3rd table
SELECT PartProgID AS [JobOrderNo], SUM(ComponentQty) AS [Qty In Process]
FROM WTLOT
GROUP BY PartProgID
Then the result become like this:

Finally i want the result be like this

So, is that possible? Your code showing me just only if the case is in 3 tables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 10:18:43

The trick is to use table aliases and derived tables like this

select		table2.joborderno,
		isnull(table1.productname, '-') as productname,
		isnull(convert(varchar, table1.duedate, 3), '-') as duedate,
		isnull(convert(varchar, table1.schedulecompletedate, 3), '-') as schedulecompletedate,
		isnull(table2.orderqty, 0) as orderqty,
		isnull(table3.[qty in progress], 0) as [qty in progress]
from		EXTERNAL_SO as table2
left join	(
			SELECT DISTINCT	PartProgID AS [JobOrderNo],
					ProductName,
					DueDate,
					ScheduledCompleteDate
			FROM		WTLOT
		) as table1 on table1.joborderno = table2.joborderno
left join	(
			SELECT		PartProgID AS [JobOrderNo],
					SUM(ComponentQty) AS [Qty In Process]
			FROM		WTLOT
			GROUP BY	PartProgID
		) as table3 on table3.joborderno = table2.joborderno
order by	table2.joborderno

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-09 : 22:20:45

Thanks your coding Peter Larsson,

In between, if i want to filter it with search engine like picture below:

so, how is my stored procedure?

** For example above,
(1)Date i need it compulsory filter and
(2)Part No and Job No is optional.

**note:
shipment date = DueDate
part no = ProductName
Job No = JobOrderNo

Hope you all helping me, really appreciate.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 00:59:30

Assuming the parameters are sent as NULL to the stored procedure when not assigned to a value...

select		table2.joborderno,
		isnull(table1.productname, '-') as productname,
		isnull(convert(varchar, table1.duedate, 3), '-') as duedate,
		isnull(convert(varchar, table1.schedulecompletedate, 3), '-') as schedulecompletedate,
		isnull(table2.orderqty, 0) as orderqty,
		isnull(table3.[qty in progress], 0) as [qty in progress]
from		EXTERNAL_SO as table2
left join	(
			SELECT DISTINCT	PartProgID AS [JobOrderNo],
					ProductName,
					DueDate,
					ScheduledCompleteDate
			FROM		WTLOT
			WHERE		DueDate >= <YourShipmentFromParameterHere>
					AND DueDate < DATEADD(day, 1, <YourShipmentToParameterHere>)
					AND (<YourPartNoParameterHere> IS NULL OR <YourPartNoParameterHere> = ProductName)
		) as table1 on table1.joborderno = table2.joborderno
left join	(
			SELECT		PartProgID AS [JobOrderNo],
					SUM(ComponentQty) AS [Qty In Process]
			FROM		WTLOT
			GROUP BY	PartProgID
		) as table3 on table3.joborderno = table2.joborderno
where		(<YourJobNoParameterHere> IS NULL OR <YourJobNoParameterHere> = table2.joborderno)
order by	table2.joborderno

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-10 : 09:30:30

Thanks again Peter,

But i test on my site, i cannot retrieve the exactly data. But no error occurs.

actually, i want is:
(1)Date (from & to) i need it compulsory filter and
--- All the data will show all based on the DueDate (From & To)

(2)Part No and Job No is optional.
--- If I either choose partno or ANY WORDS in jobno, will show the specifc records, that why i use 'LIKE"
--- If no records in this partno and jobno, then will follow all the date

below is my modify coding:

CREATE PROCEDURE rpt_Daily_AP
@prmFrDt smalldatetime,
@prmToDt smalldatetime,
@prmJobNo varchar,
@prmPartNo varchar
AS
SELECT SO.JobOrderNo,
isnull(Lot1.ProductName, '-') AS ProductName,
isnull(convert(varchar, Lot1.duedate, 3), '-') AS DueDate,
isnull(convert(varchar, Lot1.ScheduledCompleteDate, 3), '-') AS ScheduleCompleteDate,
isnull(SO.OrderQty,0) AS OrderQty,
isnull(Lot2.[Qty In Process], 0) AS [Qty In Progress],
isnull(LotF.[Finished Qty], 0) AS [Finished Qty]
FROM SMSAP..EXTERNAL_SO AS SO

LEFT JOIN
(
SELECT DISTINCT PartProgID AS [JobOrderNo],ProductName,DueDate,ScheduledCompleteDate
FROM SMSAP..WTLOT
WHERE DueDate >= @prmFrDt
AND DueDate < = @prmToDt
AND (@prmPartNo IS NULL OR @prmPartNo=ProductName)
)
AS Lot1 ON Lot1.JobOrderNo = SO.JobOrderNo

LEFT JOIN(
SELECT PartProgID AS [JobOrderNo], SUM(ComponentQty) AS [Qty In Process]
FROM SMSAP..WTLOT
GROUP BY PartProgID
)
AS Lot2 ON Lot2.JobOrderNo = SO.JobOrderNo

LEFT JOIN(
SELECT PartProgID as [JobOrderNo], SUM(ComponentQty) AS [Finished Qty]
FROM SMSAP..WTLOT_FINISH
GROUP BY PartProgID
)
AS LotF ON LotF.JobOrderNo = SO.JobOrderNo

WHERE (@prmJobNo IS NULL OR '%@prmJobNo%' LIKE SO.JobOrderNo)
ORDER BY SO.JobOrderNo
GO

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 09:55:58

WHERE (@prmJobNo IS NULL OR SO.JobOrderNo LIKE '%' + @prmJobNo + '%')

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-10 : 10:03:29

ok, i test it, whether i got enter partno or jobno, the all data will show like this:

but all the data inside are '-', but why not showing some data/value like this:

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

i think maybe is this '@prmPartNo IS NULL' and '@prmJobNo IS NULL' statement. I need is even no value inside the partno, the value also can show out

on the others hand, if have value inside this, all the related will filter out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 10:06:42

Because empty string '' is not equal to NULL.

Try

exec rpt_daily_ap '01/01/05', '01/01/08', null, null

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-10 : 10:13:42

even i try filter like this date, it should show something exactly the date, but why will show all the information that not in the filter in date range?

--------

i think maybe is this '@prmPartNo IS NULL' and '@prmJobNo IS NULL' statement. I need is even no value inside the partno, the value also can show out

on the others hand, if have value inside this, all the related will filter out

--------

but i have an idea like sample below coding, but i dunno how to attach in my part, bcoz there involve many LEFT JOIN, hope u can help me.

because i think just left some parts, then can get the result.

SET @strWhere = ' WHERE'

-- ********** Operator name ************
IF @prmLotStatus <> ''
BEGIN
SET @strWhere = @strWhere + ' ProcessingStatus IN (' + @prmLotStatus + ') AND '
END

-- ********** Date Range ************
SET @strWhere = @strWhere + ' CONVERT(varchar, StartDT, 111) BETWEEN ''' + CONVERT(varchar, @prmFromDate, 111) + ''' AND ''' + CONVERT(varchar, @prmToDate, 111) + ''' AND '

-- ***************************************

SET @strWhere = @strWhere + 'ProcessingStatus <> ''Template'''

SET @strOrder = ' ORDER BY GUID, OrderNumber, PartProgID, APPID'
SET @strQuery = @strQuery + @strWhere + @strOrder

EXEC (@strQuery)

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-10 : 10:51:31

hope that you all can guide me, because i need it urgent.

Just Thanks with you all

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 11:45:47

change all dates to ISO format (yyyymmdd) and try again. Even the parameters.

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-10 : 23:14:36

quote:
Originally posted by Peso

change all dates to ISO format (yyyymmdd) and try again. Even the parameters.

Peter Larsson
Helsingborg, Sweden

I need how to change the format? using convert?
can show me a bit?

is that you try your side, is work actually?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources