• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

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%')
0
britpopfan74
Asked:
britpopfan74
3 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now