Please Help w/UDF - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Please Help w/UDF

Author

Topic

ratstew
Starting Member

2 Posts

Posted - 2002-02-19 : 18:05:10

I am trying to write a UDF that will output a string that will ultimately be used as a primary key.... I know, why not use an integer. This is basically, what I am needing.

I need to be able to pass a function a value no larger than 2 characters. Appended to this variable will be a data time stamp in the format indicated below. The value of the datetime stamp should be Now(). The parameter and the datetime stamp should be delimited with an underscore character "_". The return value should look something like this:

FORMAT: XX_DDMMYYYYHHMMSS

~or~

RETURN VALUE EXAMPLE: LI_02192002155655
(military time preferred but not required)

LASTLY, if you are up to the challenge, or just bored, I will eventually be required to pass an additional value that can be up to 6 characters in length that will be placed inbetween the 2 character value previously discussed and the datetime stamp. Again, this will be delimited with an underscore character.

Example: LI_ABCDEF_02192002155655

If you do not do the later, no problem, I think I can implement that later based on your logic. Thank you in advance for your assistance and for this great forum.

Regards

Sean

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-19 : 18:35:48

Try this :

-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'CreateKey')
DROP FUNCTION CreateKey
GO

CREATE FUNCTION CreateKey
(@param1 varchar(2), @date datetime, @param2 varchar(6))
RETURNS varchar(24)
AS
BEGIN
DECLARE @result as varchar(24)
DECLARE @day as varchar(2)
DECLARE @month as varchar(2)
DECLARE @year as varchar(4)
DECLARE @hour as varchar(2)
DECLARE @min as varchar(2)
DECLARE @sec as varchar(2)
DECLARE @conv_date varchar(14)

Select @day = Datepart(dd,@date)
Select @month = Datepart(mm,@date)
Select @year = Datepart(yyyy,@date)
Select @hour = Datepart(hh,@date)
Select @min = Datepart(mi,@date)
Select @sec = Datepart(ss,@date)

Select @conv_date = Replicate(0,2-Len(@day)) + @day + Replicate(0,2-Len(@month))+ @month + @year + Replicate(0,2-Len(@hour)) + @hour + Replicate(0,2-Len(@min)) + @min + Replicate(0,2-Len(@sec)) + @sec

If @param2 = ''
Select @result = @param1 + '_' + @conv_date
Else
Select @result = @param1 + '_' + @param2 + '_' + @conv_date

Return @result
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.CreateKey
('LX',getdate(),'ABCDEF')
GO

There is probably a much easier way to get the date into the right format !!

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-02-19 : 18:43:15

Try the following:

select 'XX' + '_' + 'YYYYYY' + '_' + left(replace(convert(varchar,getdate(),110),'-','') +
replace(convert(varchar,getdate(),14),':',''),14)

quote:

There is probably a much easier way to get the date into the right format !!

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-19 : 18:49:29

jb,

You cannot use getdate() in a UDF.

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-19 : 18:50:56

See, I said there must be an easier way !!!

Using jbkaynes snazzy date bit the function would be:

-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'CreateKey')
DROP FUNCTION CreateKey
GO

CREATE FUNCTION CreateKey
(@param1 varchar(2), @date datetime, @param2 varchar(6))
RETURNS varchar(24)
AS
BEGIN
DECLARE @result as varchar(24)

If @param2 = ''
Select @result = @param1 + '_' + left(replace(convert(varchar,@date,110),'-','') + replace(convert(varchar,@date,14),':',''),14)
Else
Select @result = @param1 + '_' + @param2 + '_' + left(replace(convert(varchar,@date,110),'-','') + replace(convert(varchar,@date,14),':',''),14)

Return @result
END
GO

ratstew
Starting Member

2 Posts

Posted - 2002-02-20 : 10:47:35

Ahhhh --- you guys rock!!!!!!! Note to self, CAN NOT USE GetDate() IN UDF. Thank you all for your help, worked like a champ.

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources