CODEWITHSUNDEEP
sql-servert-sql
noname
noname

Reputation: 57

Return a table in a function

CREATE FUNCTION dbo.GetCustomers
(
  @ZIPCodePattern VARCHAR(255)
)
RETURNS TABLE(How i return as dbo.Customers)

AS BEGIN
manipulating ZIPCodePattern ...
SELECT * FROM dbo.Customers
WHERE dbo.RegExFunctionMatch(dbo.Customers.ZIPCODE, @ZIPCodePattern ) = 1
RETURN(How i retrun all where my function returns 1) 
END

This function have to return all dbo.Customers rows where ZIPCODE match a pattern. I manipulate the var ZIPCodePattern before i compare it. Im not familar with the syntax. Please help.

Upvotes: 0

Views: 125

Answers (1)

codingbadger
codingbadger

Reputation: 43974

See Create Function in MSDN

You need to do something like this for single statement select:

CREATE FUNCTION dbo.GetCustomers
(
  @ZIPCodePattern VARCHAR(255)
)
RETURNS TABLE

AS 

RETURN
(
-- manipulating ZIPCodePattern ...
SELECT * FROM dbo.Customers
WHERE dbo.RegExFunctionMatch(dbo.Customers.ZIPCODE, @ZIPCodePattern ) = 1
);
GO

or if you are using multiple SQL Statements then you need explicitly define your table structure:

CREATE FUNCTION dbo.GetCustomers
(
  @ZIPCodePattern VARCHAR(255)
)
RETURNS @TableToReturn TABLE
(
-- Define your table structure here
CustomerId int primary key not null,
CustomerName varchar(200) not null,
-- ...

) 

AS 
BEGIN

-- Do some stuff with your @ZipPattern
if (@ZipPattern != Null)
begin

 -- do something

end


Insert Into @TableToReturn 
SELECT * FROM dbo.Customers
WHERE dbo.RegExFunctionMatch(dbo.Customers.ZIPCODE, @ZIPCodePattern ) = 1



Select * From @TableToReturn


END
GO

Upvotes: 2

Related Questions