Having difficulty call ing subroutines 1

[Tomadams]

Hi,

I can't call a subroutine using a case statement. Will you look at my code and tell me what I am doing wrong?
Thanks

insert into #ReturnsDetail (
[Build_Date]
)
select
case [Prod_Line]
when 'PT' then
set @SerNo = a.[Serial_No]
exec sp_PT_Date_Built @SerNo, @DateBuilt OUTPUT
when 'OP' then
set @SerNo = a.[Serial_No]
exec sp_OP_Date_Built @SerNo, @DateBuilt OUTPUT
end
from
#ReturnsDetail

 

[gmmastros]

Your syntax is all wrong, but I can't seem to understand what you are trying to do either.

Case/when is used for data. If you want to control the flow of code, then you need to use IF/Then.

I suggest you explain (in greater detail) what you are trying to do. Sample data and expected results would also be useful.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

You can't inject a SET or EXEC statement into a SELECT statement, which is what your code would do.
Also, I don't see the a. alias anywhere in your statement.

Instead, try
declare @prod_line varchar(2)
select @serNo =

a.[Serial_No]
,
@prod_line = prod_line
From #ReturnsDetail

If @prod_line = 'PT'
exexec sp_PT_Date_Built @SerNo, @DateBuilt OUTPUT
if @prod_line = 'OP'
exec sp_OP_Date_Built @SerNo, @DateBuilt OUTPUT


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[philhege]

And George beats me again. I need to type faster, or stop working while I'm posting to TT ;^)

 

[Tomadams]

Here is my entire proceedure. What I am trying to do is create a dataset that will have the age of a tool that has been returned. Age = Date_Returned - Date Built. The date the tool is built is in the serial number, but in two different formats, so I need to select the correct format. Actually I knwo very little about T-SQL (but, you already know that) I am taking other code from a consultant that I brought in and trying to reconstruct something.

CREATE procedure [dbo].[SELAgingDataPr] (
-- possible filters on product
@Prod_Line [nvarchar] (50) = '*',
@Brand [nvarchar] (50) = '*',
@Tool_Family [nvarchar] (50) = '*',
@Tool_Type [nvarchar] (50) = '*',
@SKU [nvarchar] (50) = '*',
@Model_No [nvarchar] (50) = '*',
@Obsolete [int] = 0,

-- possible filters on error
@Defect_Cat [nvarchar] (50) = '*',
@Defect_Type [nvarchar] (50) = '*',
@Defect_Detail [nvarchar] (65) = '*',

-- possible date ranges
@StartDate [datetime] = '01/01/1899',
@StopDate [datetime] = '01/01/1899',

-- (added 10/8/2004) possible manufacturing location
@Mfg_Location [nvarchar] (50) = '*',

-- possible prefix for "period" label (for reportnet sorting)
@PeriodPrefix [nvarchar] (50) = '*',

-- added to determine which return table date to use
@UseDateField_DateOfReturn [int] = 1,
@UseDateField_Date_Stamp [int] = 0,
@UseDateField_DateBuilt [int] = 0,

-- if the data is to be broken around the start date... as before and after
@BreakTimePeriodsAroundStartDate [int] = 0
)
as
declare
@obs bit,
@SerNo [nvarchar],
@DateBuilt datetime
begin
-- don't display row counters
set nocount on

-- holder table for analysis data
create table #tblCatalogTable (
[Prod_Line] [nvarchar] (50) NULL ,
[Brand] [nvarchar] (50) NULL ,
[Tool_Family] [nvarchar] (50) NULL ,
[Tool_Type] [nvarchar] (50) NULL ,
[Model_No] [nvarchar] (50) NULL ,
[SKU] [nvarchar] (50) NULL,
[Mfg_Location] [nvarchar] (50) NULL
)

-- get the product information
insert into #tblCatalogTable (
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[Model_No],
[SKU],
[Mfg_Location]
)
select
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[Model_No],
[SKU],
isnull([Mfg_Location],'')
from
Catalog
where
((Prod_Line = @Prod_Line) or (@Prod_Line = '*')) and
((Brand = @Brand) or (@Brand = '*')) and
((Tool_Family = @Tool_Family) or (@Tool_Family = '*')) and
((Tool_Type = @Tool_Type) or (@Tool_Type = '*')) and
((SKU = @SKU) or (@SKU = '*')) and
((Model_No = @Model_No) or (@Model_No = '*')) and
((Obsolete = @obs) or (@obs is null)) and
((Mfg_Location = @Mfg_Location) or (@Mfg_Location = '*')) and
(((system_user=N'cgsosuser') and (Manufactured=0)) or (system_user<>N'cgsosuser'))

-- holder table for analysis data
create table #RETURNS (
[Model_No] [nvarchar] (50) NULL ,
[Serial_No] [nvarchar] (50) NULL,
[Return_Date] [datetime] NOT NULL
)

-- get the filtered analysis data
insert into #RETURNS (
Model_No,
Serial_No,
Return_Date
)
select
Model_No,
Serial_No,
Return_Date
From
RETURNS_WITH_SERIAL
where
Return_Date >= @StartDate and
Return_Date <= @StopDate

-- holder for final sales analysis detail
create table #ReturnsDetail (
[Prod_Line] [nvarchar] (50) NULL ,
[Brand] [nvarchar] (50) NULL ,
[Tool_Family] [nvarchar] (50) NULL ,
[Tool_Type] [nvarchar] (50) NULL ,
[SKU] [nvarchar] (50) NULL,
[Model_No] [nvarchar] (50) NULL ,
[Serial_No] [nvarchar] (50) NULL ,
[Return_Date] [datetime],
[Build_Date] [datetime]
)

-- combine all of the records
insert into #ReturnsDetail (
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date]
)
select
b.[Prod_Line],
b.[Brand],
b.[Tool_Family],
b.[Tool_Type],
b.[SKU],
a.[Model_No],
a.[Serial_No],
a.[Return_Date]
from
#RETURNS a
join
#tblCatalogTable b
on
a.Model_No = b.Model_No

insert into #ReturnsDetail (
[Build_Date]
)
select
case [Prod_Line]
when 'PT' then
'2006-01-01'
-- set @SerNo = a.[Serial_No]
-- exec sp_PT_Date_Built @SerNo, @DateBuilt OUTPUT
when 'OP' then
-- set @SerNo = a.[Serial_No]
-- exec sp_OP_Date_Built @SerNo, @DateBuilt OUTPUT
'2007-01-01'
end
from
#ReturnsDetail
--Update #ReturnsDetail
-- set Build_Date =


create table #ReturnsSummary (
[Prod_Line] [nvarchar] (50) NULL ,
[Brand] [nvarchar] (50) NULL ,
[Tool_Family] [nvarchar] (50) NULL ,
[Tool_Type] [nvarchar] (50) NULL ,
[SKU] [nvarchar] (50) NULL,
[Model_No] [nvarchar] (50) NULL ,
[Serial_No] [nvarchar] (50) NULL ,
[Return_Date] [datetime],
[Build_Date] [datetime],
[Age] [int]
)
insert into #ReturnsSummary (
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date],
[Build_Date],
[Age]
)
select
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date],
[Build_Date],
datediff(mm,build_Date,Return_Date) as Age
from
#ReturnsDetail

-- return the results
select
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date],
[Build_Date],
[Age]
from
#ReturnsSummary
order by
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No]


-- cleanup temp tables
drop table #tblCatalogTable
drop table #RETURNS
drop table #ReturnsDetail
drop table #ReturnsSummary
end
GO

 

[ca8msm]

OK, so have you tried implementing the If/Then statements that were suggested to you above?


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Tomadams]

Yes, but I cant syntax errors when I change the select to:
select
if [Prod_Line] = 'PT'
set @SerNo = a.[Serial_No]
exec sp_PT_Date_Built @SerNo, @DateBuilt OUTPUT
else
set @SerNo = a.[Serial_No]
exec sp_OP_Date_Built @SerNo, @DateBuilt OUTPUT
end
from
#ReturnsDetail

 

[gmmastros]

On average, how many records will be in #ReturnsDetail each time this procedure is called? More specifically, will there always be exactly 1 record, or will there be more?

Also, can you show us the 2 stored procedures: sp_PT_Date_Built and sp_OP_Date_Built

I'm thinking that it may make more sense to create a user defined function that you pass the Product Line and Serial Number to, and it returns the DateCreated. If you create this function, then you wouldn't need to do any special handling for the create date.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

You can't insert If/Then statements inside a select query. They need to be contained outside, like philhege's example showed above.


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Tomadams]

Record count will be thousands of records.

Here is sp_OP_Date_Built, The both work when I test then with query analyzer.
CREATE PROCEDURE [dbo].[sp_OP_Date_Built]
(
@SerNo nvarchar(50),
@datebuilt datetime out
)
AS
declare
@nMonth int,
@nYear int,
@nDay int,
@DateCode nvarchar(20),
@YearCode nvarchar(1),
@hMonth nvarchar(20),
@sMonth nvarchar(20),
@aMonth nvarchar(20),
@sYear nvarchar(20)
begin
set nocount on

set @yearCode = substring(@SerNo,3,1)
If @YearCode BETWEEN 'A' and 'Z'
Begin
Select @sYear =
Case substring(@SerNo, 3, 1)
when 'J' then '2006'
when 'K' then '2007'
when 'L' then '2008'
when 'M' then '2009'
when 'N' then '2010'
Else '2005'
End
set @sMonth = substring(@SerNo, 4, 2)
end
else
Begin
set @YearCode = substring(@SerNo,2,1)
Select @sYear =
Case @YearCode
when 'J' then '2006'
when 'K' then '2007'
when 'L' then '2008'
when 'M' then '2009'
when 'N' then '2010'
Else '2005'
End
set @sMonth = SubString(@SerNo, 3, 2)
End

Select @nMonth =
case @sMonth
when '01' then 1
when '02' then 1
when '03' then 1
when '04' then 1
when '05' then 1
when '06' then 2
when '07' then 2
when '08' then 2
when '09' then 2
when '10' then 3
when '11' then 3
when '12' then 3
when '13' then 3
when '14' then 3
when '15' then 4
when '16' then 4
when '17' then 4
when '18' then 4
when '19' then 5
when '20' then 5
when '21' then 5
when '22' then 5
when '23' then 6
when '24' then 6
when '25' then 6
when '26' then 6
when '27' then 6
when '28' then 7
when '29' then 7
when '30' then 7
when '31' then 7
when '32' then 8
when '33' then 8
when '34' then 8
when '35' then 8
when '36' then 9
when '37' then 9
when '38' then 9
when '39' then 9
when '40' then 9
when '41' then 10
when '42' then 10
when '43' then 10
when '44' then 10
when '45' then 11
when '46' then 11
when '47' then 11
when '48' then 11
else 12
end

set @sMonth = cast(@nMonth as varchar)
set @dateCode = @sMonth + '/01/' + @sYear
set @datebuilt = cast(@DateCode as datetime)
end
GO

 

[gmmastros]

That procedure is ugly! Ugly because there's a lot of code and it appears to be rather complex, but I assume that it works properly, so I would suggest that you 'pretty much' leave it alone. As such, I would recommend that you convert this procedure to a user defined function. Do the same thing with the other procedure (sp_PT_Date_Built) which I assume is similar to this one, but pulls data from other parts of the serial number.

Then, make another function that you pass the product line and serial number to. This 3rd function would simply call one (or the other) DateBuilt functions and return the Create Date from it.

Ex:

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] dbo.udf_GetDateBuilt
	(
	@ProductLine [COLOR=blue]VarChar[/color](2),
	@SerialNumber nvarchar(50)
	)
Returns [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]
	[COLOR=blue]Declare[/color] @DateCreated [COLOR=#FF00FF]DateTime[/color]

	[COLOR=blue]If[/color] @ProductLine = [COLOR=red]'OP'[/color] 
		[COLOR=blue]Set[/color] @DateCreated = dbo.udf_OP_DateBuilt(@SerialNumber)

	[COLOR=blue]If[/color] @ProductLine = [COLOR=red]'PT'[/color]
		[COLOR=blue]Set[/color] @DateCreated = dbo.udf_DT_DateBuilt(@SerialNumber)

	[COLOR=blue]Return[/color] @DateCreated
[COLOR=blue]End[/color]

To convert your existing stored procedure to a function....

[COLOR=blue]CREATE[/color] [!]FUNCTION[/!] [dbo].[udf_OP_Date_Built]
    (
    @SerNo nvarchar(50)
    )
[!]Returns DateTime[/!]
[COLOR=blue]AS[/color]
[COLOR=blue]Begin[/color]
[COLOR=blue]declare[/color]
    @nMonth [COLOR=blue]int[/color],
    @nYear [COLOR=blue]int[/color],
    @nDay [COLOR=blue]int[/color],
    @DateCode nvarchar(20),
    @YearCode nvarchar(1),
    @hMonth nvarchar(20),
    @sMonth nvarchar(20),
    @aMonth nvarchar(20),
    @sYear nvarchar(20)

    [COLOR=blue]set[/color] @yearCode = [COLOR=#FF00FF]substring[/color](@SerNo,3,1)
    [COLOR=blue]If[/color] @YearCode  BETWEEN [COLOR=red]'A'[/color] and [COLOR=red]'Z'[/color]
                [COLOR=blue]Begin[/color]
        [COLOR=blue]Select[/color] @sYear =
        [COLOR=blue]Case[/color] [COLOR=#FF00FF]substring[/color](@SerNo, 3, 1)
                            [COLOR=blue]when[/color] [COLOR=red]'J'[/color]  [COLOR=blue]then[/color]  [COLOR=red]'2006'[/color]
                            [COLOR=blue]when[/color] [COLOR=red]'K'[/color] [COLOR=blue]then[/color] [COLOR=red]'2007'[/color]
                            [COLOR=blue]when[/color] [COLOR=red]'L'[/color] [COLOR=blue]then[/color]  [COLOR=red]'2008'[/color]
                         [COLOR=blue]when[/color] [COLOR=red]'M'[/color] [COLOR=blue]then[/color] [COLOR=red]'2009'[/color]
                   [COLOR=blue]when[/color] [COLOR=red]'N'[/color] [COLOR=blue]then[/color]  [COLOR=red]'2010'[/color]
                 [COLOR=blue]Else[/color]  [COLOR=red]'2005'[/color]
                    [COLOR=blue]End[/color]
        [COLOR=blue]set[/color] @sMonth = [COLOR=#FF00FF]substring[/color](@SerNo, 4, 2)
    [COLOR=blue]end[/color]
    [COLOR=blue]else[/color]
    [COLOR=blue]Begin[/color]
        [COLOR=blue]set[/color] @YearCode = [COLOR=#FF00FF]substring[/color](@SerNo,2,1)
        [COLOR=blue]Select[/color] @sYear =
           [COLOR=blue]Case[/color] @YearCode
                            [COLOR=blue]when[/color] [COLOR=red]'J'[/color]  [COLOR=blue]then[/color] [COLOR=red]'2006'[/color]
                        [COLOR=blue]when[/color] [COLOR=red]'K'[/color] [COLOR=blue]then[/color] [COLOR=red]'2007'[/color]
                            [COLOR=blue]when[/color] [COLOR=red]'L'[/color] [COLOR=blue]then[/color] [COLOR=red]'2008'[/color]
                        [COLOR=blue]when[/color] [COLOR=red]'M'[/color] [COLOR=blue]then[/color] [COLOR=red]'2009'[/color]
                   [COLOR=blue]when[/color] [COLOR=red]'N'[/color] [COLOR=blue]then[/color] [COLOR=red]'2010'[/color]
                 [COLOR=blue]Else[/color]   [COLOR=red]'2005'[/color]
                    [COLOR=blue]End[/color]
        [COLOR=blue]set[/color] @sMonth = [COLOR=#FF00FF]SubString[/color](@SerNo, 3, 2)
    [COLOR=blue]End[/color]
    
    [COLOR=blue]Select[/color] @nMonth =
        [COLOR=blue]case[/color] @sMonth
            [COLOR=blue]when[/color] [COLOR=red]'01'[/color] [COLOR=blue]then[/color] 1
            [COLOR=blue]when[/color] [COLOR=red]'02'[/color] [COLOR=blue]then[/color] 1
            [COLOR=blue]when[/color] [COLOR=red]'03'[/color] [COLOR=blue]then[/color] 1
            [COLOR=blue]when[/color] [COLOR=red]'04'[/color] [COLOR=blue]then[/color] 1
            [COLOR=blue]when[/color] [COLOR=red]'05'[/color] [COLOR=blue]then[/color] 1
            [COLOR=blue]when[/color] [COLOR=red]'06'[/color] [COLOR=blue]then[/color] 2
            [COLOR=blue]when[/color] [COLOR=red]'07'[/color] [COLOR=blue]then[/color] 2
            [COLOR=blue]when[/color] [COLOR=red]'08'[/color] [COLOR=blue]then[/color] 2
            [COLOR=blue]when[/color] [COLOR=red]'09'[/color] [COLOR=blue]then[/color] 2
            [COLOR=blue]when[/color] [COLOR=red]'10'[/color] [COLOR=blue]then[/color] 3
            [COLOR=blue]when[/color] [COLOR=red]'11'[/color] [COLOR=blue]then[/color] 3
            [COLOR=blue]when[/color] [COLOR=red]'12'[/color] [COLOR=blue]then[/color] 3
            [COLOR=blue]when[/color] [COLOR=red]'13'[/color] [COLOR=blue]then[/color] 3
            [COLOR=blue]when[/color] [COLOR=red]'14'[/color] [COLOR=blue]then[/color] 3
            [COLOR=blue]when[/color] [COLOR=red]'15'[/color] [COLOR=blue]then[/color] 4
            [COLOR=blue]when[/color] [COLOR=red]'16'[/color] [COLOR=blue]then[/color] 4
            [COLOR=blue]when[/color] [COLOR=red]'17'[/color] [COLOR=blue]then[/color] 4
            [COLOR=blue]when[/color] [COLOR=red]'18'[/color] [COLOR=blue]then[/color] 4
            [COLOR=blue]when[/color] [COLOR=red]'19'[/color] [COLOR=blue]then[/color] 5
            [COLOR=blue]when[/color] [COLOR=red]'20'[/color] [COLOR=blue]then[/color] 5
            [COLOR=blue]when[/color] [COLOR=red]'21'[/color] [COLOR=blue]then[/color] 5
            [COLOR=blue]when[/color] [COLOR=red]'22'[/color] [COLOR=blue]then[/color] 5
            [COLOR=blue]when[/color] [COLOR=red]'23'[/color] [COLOR=blue]then[/color] 6
            [COLOR=blue]when[/color] [COLOR=red]'24'[/color] [COLOR=blue]then[/color] 6
            [COLOR=blue]when[/color] [COLOR=red]'25'[/color] [COLOR=blue]then[/color] 6
            [COLOR=blue]when[/color] [COLOR=red]'26'[/color] [COLOR=blue]then[/color] 6
            [COLOR=blue]when[/color] [COLOR=red]'27'[/color] [COLOR=blue]then[/color] 6
            [COLOR=blue]when[/color] [COLOR=red]'28'[/color] [COLOR=blue]then[/color] 7
            [COLOR=blue]when[/color] [COLOR=red]'29'[/color] [COLOR=blue]then[/color] 7
            [COLOR=blue]when[/color] [COLOR=red]'30'[/color] [COLOR=blue]then[/color] 7
            [COLOR=blue]when[/color] [COLOR=red]'31'[/color] [COLOR=blue]then[/color] 7
            [COLOR=blue]when[/color] [COLOR=red]'32'[/color] [COLOR=blue]then[/color] 8
            [COLOR=blue]when[/color] [COLOR=red]'33'[/color] [COLOR=blue]then[/color] 8
            [COLOR=blue]when[/color] [COLOR=red]'34'[/color] [COLOR=blue]then[/color] 8
            [COLOR=blue]when[/color] [COLOR=red]'35'[/color] [COLOR=blue]then[/color] 8
            [COLOR=blue]when[/color] [COLOR=red]'36'[/color] [COLOR=blue]then[/color] 9
            [COLOR=blue]when[/color] [COLOR=red]'37'[/color] [COLOR=blue]then[/color] 9
            [COLOR=blue]when[/color] [COLOR=red]'38'[/color] [COLOR=blue]then[/color] 9
            [COLOR=blue]when[/color] [COLOR=red]'39'[/color] [COLOR=blue]then[/color] 9
            [COLOR=blue]when[/color] [COLOR=red]'40'[/color] [COLOR=blue]then[/color] 9
            [COLOR=blue]when[/color] [COLOR=red]'41'[/color] [COLOR=blue]then[/color] 10
            [COLOR=blue]when[/color] [COLOR=red]'42'[/color] [COLOR=blue]then[/color] 10
            [COLOR=blue]when[/color] [COLOR=red]'43'[/color] [COLOR=blue]then[/color] 10
            [COLOR=blue]when[/color] [COLOR=red]'44'[/color] [COLOR=blue]then[/color] 10
            [COLOR=blue]when[/color] [COLOR=red]'45'[/color] [COLOR=blue]then[/color] 11
            [COLOR=blue]when[/color] [COLOR=red]'46'[/color] [COLOR=blue]then[/color] 11
            [COLOR=blue]when[/color] [COLOR=red]'47'[/color] [COLOR=blue]then[/color] 11
            [COLOR=blue]when[/color] [COLOR=red]'48'[/color] [COLOR=blue]then[/color] 11
            [COLOR=blue]else[/color] 12
        [COLOR=blue]end[/color]

    [COLOR=blue]set[/color] @sMonth = [COLOR=#FF00FF]cast[/color](@nMonth [COLOR=blue]as[/color] [COLOR=blue]varchar[/color])
    [COLOR=blue]set[/color] @dateCode = @sMonth + [COLOR=red]'/01/'[/color] + @sYear
    [!]Return[/!] [COLOR=#FF00FF]cast[/color](@DateCode [COLOR=blue]as[/color] [COLOR=#FF00FF]datetime[/color])
[COLOR=blue]end[/color]
[COLOR=blue]GO[/color]

Once these are converted to UDF's, you can use them in the outer procedure like this...

Change:
[tt] -- combine all of the records
insert into #ReturnsDetail (
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date]
)
select
b.[Prod_Line],
b.[Brand],
b.[Tool_Family],
b.[Tool_Type],
b.[SKU],
a.[Model_No],
a.[Serial_No],
a.[Return_Date]
from
#RETURNS a
join
#tblCatalogTable b
on
a.Model_No = b.Model_No

insert into #ReturnsDetail (
[Build_Date]
)
select
case [Prod_Line]
when 'PT' then
'2006-01-01'
-- set @SerNo = a.[Serial_No]
-- exec sp_PT_Date_Built @SerNo, @DateBuilt OUTPUT
when 'OP' then
-- set @SerNo = a.[Serial_No]
-- exec sp_OP_Date_Built @SerNo, @DateBuilt OUTPUT
'2007-01-01'
end
from
#ReturnsDetail
--Update #ReturnsDetail
-- set Build_Date =
[/tt]

To:

    [COLOR=green]-- combine all of the records
[/color]    [COLOR=blue]insert[/color] [COLOR=blue]into[/color] #ReturnsDetail (
        [Prod_Line],
        [Brand],
        [Tool_Family],
        [Tool_Type],
        [SKU],
        [Model_No],
        [Serial_No],
        [Return_Date],
        [!][Build_Date][/!]
    )
    [COLOR=blue]select[/color]
        b.[Prod_Line],
        b.[Brand],
        b.[Tool_Family],
        b.[Tool_Type],
        b.[SKU],
        a.[Model_No],
        a.[Serial_No],
        a.[Return_Date],
        [!]dbo.udf_GetDateBuilt(b.[Prod_Line], a.[Serial_No])[/!]
    [COLOR=blue]from[/color]
        #RETURNS a
    [COLOR=blue]join[/color]
        #tblCatalogTable b
    [COLOR=blue]on[/color]
        a.Model_No = b.Model_No

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tomadams]

George - Thanks much - and to all that took so much time for me. I will begin to recode and then try to let you know how it worked. Also, can anyone reccommend a good manual for a guy like me?

Thanks - Tom

 

[Tomadams]

I am getting the following errror when I try to save the stored prpocedure:
Error 21001: [SQL-DMO]Stsored procedure definition must include name and text )for Standard StoredProcedure) of libraryname (for Extended StoredProcedure).

I folowed the example TO CREATE A USER DEFINED DUNCTION exactly!!

 

[gmmastros]

Instead of using Enterprise Manager, I recommend you use Query Analyzer.

In Enterprise Manager, drill down to your database, then click Tools -> SQL Query Analyzer

At that point, you should be able to copy/paste the code samples above.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

Instead of using Enterprise Manager, I recommend you use Query Analyzer.
.
.
.
In Enterprise Manager.....

Sounds like someone has a case of the Mondays.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[gmmastros]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom