user defined function default parameter

[jacck]

i have created a user defined function that returns a table. There is one parameter (@Job CHAR(10) )

SELECT * from dbo.fproduction_record(123456) returns the correct results for that job # 123456

For testing purposes i want to be able to return all results of the function but when i try to use DEFAULT as the parameter
SELECT * from dbo.fproduction_record(default)it produces zero results.

How should the default parameter be used to return the full dataset

Also, if I also wanted to return the results for a range of jobs, how would i type the select statement?

thanks for any help

 

[vongrunt]

This is easy... post function code you have so far.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[jacck]

sorry, don't follow that.

what is the replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/') bit

tried typing:
select * from dbo.fproduction_record(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/') but getting syntax error

please expand

 

[BillyRayPreachersSon]

That's what is commonly known to those who know about posting on the internet as a signature. It is not part of the post, and wasn't intended to be entered.

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

[tt]Dan's Page [blue]@[/blue] Code Couch

http://www.codecouch.com/dan/

[/tt]

 

[jacck]

well now you all know how much i know - obvious beginner !

and i don't even have a signature!

getting back to the question, is vongrunt asking me to post all the code that i have in the function ?

 

[BillyRayPreachersSon]

Do you *really* need someone to answer that? I thought his post was fairly self-explanatory.

Dan




Coedit Limited - Delivering standards compliant, accessible web solutions

[tt]Dan's Page [blue]@[/blue] Code Couch

http://www.codecouch.com/dan/

[/tt]

 

[jacck]

sorry - better brush up on my posting technique
in the meantime, here's the function:

CREATE FUNCTION dbo.fProduction_Record (@job char(10) = NULL)
RETURNS table AS
return (
SELECT TOP 100 PERCENT dbo.JOBS.JOB_NUMBER AS Job, dbo.JOB_OUTPUTS.PART_CODE AS Part,
dbo.PM_JOB_TO_ORDER_XREF.CUSTOMERS_PART_CODE AS Cust_Part, fin_prod.dbo.IC_LOT_CHARACTERISTICS.CHARACTERISTIC_TEXT AS RMLot,
dbo.JOB_STAGE_LINE_MATERIAL.IC_LOT_NUMBER AS Input_Fablot, dbo.PM_JOB_TO_ORDER_XREF.CUSTOMER_NUMBER AS Customer,
dbo.PM_JOB_TO_ORDER_XREF.ORDER_NUMBER AS SO, dbo.PM_JOB_TO_ORDER_XREF.ORDER_LINE_NUMBER AS SO_Line,
dbo.PM_JOB_TO_ORDER_XREF.SO_SEQUENCE AS SO_Rel, dbo.JOBS.STD_PROCESS_SPEC AS P_Spec,
fin_prod.dbo.IC_LOT_CHARACTERISTICS.IC_LOT_NUMBER as Output_Lot, SUBSTRING(dbo.JOB_STAGE_LINE_MATERIAL.IC_LOT_NUMBER, 6, 6) AS Input_Job, dbo.JOB_STAGE_LINE_MATERIAL.WAREHOUSE
FROM dbo.PM_JOB_TO_ORDER_XREF RIGHT OUTER JOIN
dbo.JOB_OUTPUTS LEFT OUTER JOIN
fin_prod.dbo.IC_LOT_CHARACTERISTICS ON dbo.JOB_OUTPUTS.PART_CODE = fin_prod.dbo.IC_LOT_CHARACTERISTICS.PART_CODE AND
dbo.JOB_OUTPUTS.COMPANY_CODE = fin_prod.dbo.IC_LOT_CHARACTERISTICS.COMPANY_CODE AND
fin_prod.dbo.IC_LOT_CHARACTERISTICS.WAREHOUSE in ('B6','C7') AND
dbo.JOB_OUTPUTS.JOB_NUMBER = SUBSTRING(fin_prod.dbo.IC_LOT_CHARACTERISTICS.IC_LOT_NUMBER, 6, 6) AND
fin_prod.dbo.IC_LOT_CHARACTERISTICS.CHARACTERISTIC_CODE = 'RMLOT1' RIGHT OUTER JOIN
dbo.JOB_STAGE_LINE_MATERIAL RIGHT OUTER JOIN
dbo.JOBS ON dbo.JOB_STAGE_LINE_MATERIAL.COMPANY_CODE = dbo.JOBS.COMPANY_CODE AND
dbo.JOB_STAGE_LINE_MATERIAL.FACTORY = dbo.JOBS.FACTORY AND
dbo.JOB_STAGE_LINE_MATERIAL.JOB_NUMBER = dbo.JOBS.JOB_NUMBER AND not (dbo.JOB_STAGE_LINE_MATERIAL.WAREHOUSE in ('B2','B4'))
and dbo.JOB_STAGE_LINE_MATERIAL.INPUT_QTY_ACTUAL<> dbo.JOB_STAGE_LINE_MATERIAL.PM_RETURNED_QTY ON
dbo.JOB_OUTPUTS.COMPANY_CODE = dbo.JOBS.COMPANY_CODE AND dbo.JOB_OUTPUTS.FACTORY = dbo.JOBS.FACTORY AND
dbo.JOB_OUTPUTS.JOB_NUMBER = dbo.JOBS.JOB_NUMBER AND dbo.JOB_OUTPUTS.DESTINATION_TYPE = 'I' AND
dbo.JOB_OUTPUTS.FINAL_PRODUCT_FLAG = 'Y' ON dbo.PM_JOB_TO_ORDER_XREF.COMPANY_CODE = dbo.JOBS.COMPANY_CODE AND
dbo.PM_JOB_TO_ORDER_XREF.FACTORY = dbo.JOBS.FACTORY AND
dbo.PM_JOB_TO_ORDER_XREF.JOB_NUMBER = dbo.JOBS.JOB_NUMBER
WHERE (dbo.JOBS.COMPANY_CODE = '2') AND (dbo.JOBS.FACTORY = 'P1') AND(@job = dbo.JOBS.JOB_NUMBER)
)

thanks

 

[vongrunt]

Change last AND condition in WHERE clause to:

AND([!]@job IS NULL OR[/!] @job = dbo.JOBS.JOB_NUMBER)

Btw. such code is kinda more suitable for stored procs...

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[jacck]

Thanks very much

Can i use the parameter in the same way if its a stored procedure?

I'll get back to my SQL Bible and do some more reading - i'm no programmer as you can tell, just a dangerous user.

Thanks again

 

[vongrunt]

> Can i use the parameter in the same way if its a stored procedure?

Not exactly the same way... but very similar. Sproc will look like:

CREATE PROCEDURE  dbo.uspProduction_Record (@job char(10) = NULL)
AS
SET NOCOUNT ON -- always use this
<SELECT query here>
GO

And sample calls are:

-- with specified job (2 ways)
exec uspProduction_Record 'somejob'
exec uspProduction_Record @job = 'somejob'

-- with default value
exec uspProduction_Record

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[jacck]

that's great.. thanks a lot for your help

- i'll try this tonight