SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Function Help (adventureworks db)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  19:03:40  Show Profile  Reply with Quote
Hi All,

I am trying to set up a function with a input and output variable, I am using adventureworks2008r2, person.person table

CREATE FUNCTION MyFunction(@someValue datetime)
RETURNS TABLE
AS
BEGIN
DECLARE @retval INTEGER

SELECT FirstName,LastName
FROM person.person
WHERE ModifiedDate = @someValue

RETURN @retval
END

could someone point me in the right direction, and how to then test the function to make sure if i enter a date it will return the first name and last name from the table


Thanks,
Chris
Jr Programmer

Edited by - Chris_Kelley on 08/28/2014 19:04:22

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  19:17:45  Show Profile  Reply with Quote
DROP FUNCTION MYFUNCTION

CREATE FUNCTION MYFUNCTION(@SOMEVALUE NVARCHAR(200))
RETURNS
@PERSON TABLE (
[FIRSTNAME] [NVARCHAR](50),[LASTNAME] [NVARCHAR](50)
)
AS
BEGIN
INSERT INTO @PERSON
SELECT [FIRSTNAME],[LASTNAME]
FROM PERSON.PERSON
WHERE CONVERT (NVARCHAR, CAST(MODIFIEDDATE AS DATE),100) = @SOMEVALUE
RETURN
END
GO

SELECT * FROM MYFUNCTION ('08/28/2008')


--GETTING CLOSER

Thanks,
Chris
Jr Programmer

Edited by - Chris_Kelley on 08/28/2014 19:22:23
Go to Top of Page

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  19:25:12  Show Profile  Reply with Quote
Got it :)

CREATE FUNCTION MYFUNCTION(@SOMEVALUE NVARCHAR(200))
RETURNS
@PERSON TABLE (
[FIRSTNAME] [NVARCHAR](50),[LASTNAME] [NVARCHAR](50)
)
AS
BEGIN
INSERT INTO @PERSON
SELECT [FIRSTNAME],[LASTNAME]
FROM PERSON.PERSON
WHERE CONVERT (NVARCHAR, CAST(MODIFIEDDATE AS DATE),100) = @SOMEVALUE
RETURN
END
GO

SELECT * FROM MYFUNCTION ('Feb 8 2003')


FIRSTNAME LASTNAME

Ken Sánchez
Eric Gubbels
Jason Watters
Tracy Tallman
Leo Giakoumakis
Michael Allen



Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  19:32:26  Show Profile  Reply with Quote
Changed the date format to 101, thought it was easier to input....

found this article which is tops if anyone wants a good date format ref

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Thanks,
Chris
Jr Programmer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000