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

Using select and if statements MS SQL Function

Hi,

I have the following funciton working fine although I want to modify it so that it runs a select statement first that checks if the Builder's status is awarded.  

From there I want to have an if statement to run either the below select statment or a differnet one based on whether or not hte builder is awarded.

Any ideas?
ALTER FUNCTION [dbo].[fnBuildersByProject]
      (@ProjectID int , @PID int  )
RETURNS varchar(1000) 
AS
BEGIN
declare @List varchar(1000)

SELECT            @List = COALESCE(@List + ', ', '') + ltrim(rtrim(convert(varchar,BuilderCompany)))
FROM      Builders
WHERE       Builders.projectID = @ProjectID
ORDER BY   Builders.BuilderCompany

RETURN @List

 

END

Open in new window

0
glit
Asked:
glit
1 Solution
 
Anthony PerkinsCommented:
Something like this:
ALTER FUNCTION [dbo].[fnBuildersByProject]
    (@ProjectID int,
     @PID int)
RETURNS varchar(1000)
AS 
BEGIN
    DECLARE @List varchar(1000)

	IF EXISTS (
		SELECT 1
		FROM   YourTable
		WHERE  BuilderStatus = 1
		)
		SELECT  @List = COALESCE(@List + ', ', '') + LTRIM(RTRIM(CONVERT(varchar, BuilderCompany)))
		FROM    Builders
		WHERE   Builders.projectID = @ProjectID
		ORDER BY Builders.BuilderCompany
	ELSE
		SELECT ...
		FROM ...
		WHERE ...
	
    RETURN @List

 

END

Open in new window

0
 
glitAuthor Commented:
That's perfect, thank you!
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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