New to functions - code runs but results not correct

Hello experts,

I just started writing functions and began with something very basic, which is pulling just a region LIKE something (probably not useful but just trying to get the idea).

When I go to execute the function, however, the results I'm getting are giving me EVERY region even though I think I'm specifying in the subquery LIKE...and then try re-iterating in the exec statement.

Please advise on what I am missing; thanks!

CREATE FUNCTION Fn_GETMAXREGION(@REGION VARCHAR(20))
RETURNS @temp table
(REGION VARCHAR(20)) AS  

BEGIN
INSERT INTO @temp
SELECT      DISTINCT REGION
FROM        Custom.DBO.VMEMBERLIST
WHERE      REGION IN
               (
                   SELECT REGION
                    FROM  Custom.DBO.VMEMBERLIST
                    WHERE @REGION LIKE '1%'
              )

RETURN
END
GO

SELECT * FROM Fn_GETMAXREGION ('1%')
britpopfan74Asked:
Who is Participating?
 
Anthony PerkinsCommented:
You create a UDF() with a schema and you execute it with the same schema, as in (assuming that dbo is the schema)

CREATE FUNCTION dbo.udf_YourFunctionNameGoesHere (
                              @Param1 <datatypegoeshere>,
                              @Param2 <datatypegoeshere>,
 ...

And to execute (assuming it is a Table Valued function):
SELECT *
FROM dbo.udf_YourFunctionNameGoesHere(@Expression1, @Expression2, ...)
0
 
ransommuleCommented:
WHERE REGION LIKE @REGION

CREATE FUNCTION Fn_GETMAXREGION(@REGION VARCHAR(20))
RETURNS @temp table
(REGION VARCHAR(20)) AS  

BEGIN
INSERT INTO @temp
SELECT      DISTINCT REGION
FROM        Custom.DBO.VMEMBERLIST
WHERE      REGION IN
               (
                   SELECT REGION
                    FROM  Custom.DBO.VMEMBERLIST
                    WHERE REGION LIKE @REGION
              )

RETURN
END
GO

I think It is not necesary add the subquery inside.

CREATE FUNCTION Fn_GETMAXREGION(@REGION VARCHAR(20))
RETURNS @temp table
(REGION VARCHAR(20)) AS  
BEGIN
INSERT INTO @temp
SELECT      DISTINCT REGION
FROM        Custom.DBO.VMEMBERLIST
WHERE       REGION LIKE @REGION

RETURN
END
GO
0
 
DcpKingCommented:
This isn't a Batch File - use the name, not the sequence number of the parameter:


SELECT      DISTINCT REGION
FROM        Custom.DBO.VMEMBERLIST
WHERE      REGION IN
               (
                   SELECT REGION
                    FROM  Custom.DBO.VMEMBERLIST
                    WHERE @REGION LIKE @REGION
              )

And that should be the same as

SELECT      DISTINCT REGION
FROM        Custom.DBO.VMEMBERLIST
WHERE      REGION  LIKE @REGION
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
britpopfan74Author Commented:
Thanks...that cleans it up but when I run:

SELECT * FROM Fn_GETMAXREGION ('1')

I get error:

Invalid object name 'Custom.DBO.VMEMBERLIST'.
0
 
ransommuleCommented:
Try to remove Custom.dbo from the query
Does exist the VMEMBERLIST table ?

SELECT  DISTINCT REGION
FROM VMEMBERLIST

Dont forget to add % in the function call

SELECT * FROM Fn_GETMAXREGION ('1%')
0
 
britpopfan74Author Commented:
it still gives me invalid object name when I remove the database name
0
 
ransommuleCommented:
USE yourdatabase

SELECT *
FROM dbo.Fn_GETMAXREGION ('1%') ?

Are you doing this in the correct server ? That is sometimes happens to me that I trying to run a query in master database ?

Does VMEMBERLIST and Fn_GETMAXREGION are in the same Database ?
0
 
britpopfan74Author Commented:
no I've had to create in different databases, hence why I need to reference the "Custom"
0
 
britpopfan74Author Commented:
Thanks for assistance...splitting points is a challenge because still trying to work with this advice which hopefully will get this working eventually.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.