Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

user defined function default parameter

Status
Not open for further replies.

jacck

Technical User
Feb 11, 2006
18
IE
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
 
This is easy... post function code you have so far.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
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



 
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 ?

 
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
 
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]
[banghead]
 
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
 
> 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:
Code:
CREATE PROCEDURE  dbo.uspProduction_Record (@job char(10) = NULL)
AS
SET NOCOUNT ON -- always use this
<SELECT query here>
GO
And sample calls are:
Code:
-- 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]
[banghead]
 
that's great.. thanks a lot for your help

- i'll try this tonight
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top