If select query equals something, slct smthg else

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

If select query equals something, slct smthg else

Author

Topic

EsQueueEll
Starting Member

2 Posts

Posted - 2010-10-04 : 13:23:37

I have these two queries that work separately but I am not sure how to combine them so they work together. The idea is if a certain value exists in my select query, I need to run another select query.



GO
CREATE FUNCTION dbo.BuildString(@Owner VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(100), U.PROMPT)
	FROM UDF U JOIN UDFValue V ON U.UDFID = V.UDFID
		
	WHERE     V.OwnerID = @Owner
	AND     V.ItemValue = 'Yes'
	AND     (U.UDFID = 10001 OR
	         U.UDFID = 10002 OR
	         U.UDFID = 10003 OR
	         U.UDFID = 10004 OR
	         U.UDFID = 10005 OR
	         U.UDFID = 10006 OR
	         U.UDFID = 10007 OR
	         U.UDFID = 10008 OR
			)

/* If U.UDFID = 10009 How can I get this to work? */
    
	ORDER BY U.PROMPT

	RETURN @Output
END
GO

UPDATE UserInformation 
SET Title = dbo.BuildString(444) WHERE UserID = 444  
GO

DROP FUNCTION dbo.BuildString
GO

Here is the other query running separately as I was trying to see if I could get it working by itself with CASE, I've tried dozens of if statements and CASE statements to get it to work in the above, but I can't get it functioning and something always results in an error. Having two queries like this separate would have a lot of overhead right?:

DECLARE @Owner VARCHAR(8000) = 444
DECLARE @Output VARCHAR(8000)

SET @Output = (select UDFID =
    case
        WHEN U.UDFID = 10009 AND V.ItemValue = 'Yes' THEN (SELECT ItemValue FROM UDFvalue WHERE UDFID = 10010 AND OwnerID = 444)
    end

FROM UDF U JOIN UDFValue V ON U.UDFID = V.UDFID

WHERE V.OwnerID = @Owner
AND   V.ItemValue = 'Yes'
)

Thanks for any help

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-05 : 12:04:48

This looks like you have a table of User Defined Function text. But that would insane; That is already in the schema tables. Then it looks like you are building a CSV list to violate First Normal Form, which is even worse.

Why are UDF Values a separate table? The name implied that they are some vague attribute of the UDF. Why do you use yes/no flags in SQL?

You might want to learn about the IN() predicate and the CAST() function. Why did you waste time and space creating a huge local variable that is not needed?

CREATE FUNCTION dbo.BuildString -- of what?
(@in_owner_id VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
RETURN
(SELECT ', ' + CAST(UDF.prompt AS VARCHAR(100))
FROM UDF, UDF_Values AS V
WHERE UDF.udf_id = V.udf_id
AND V.owner_id = @in_owner_id
AND V.item_value = 'Yes'
AND UDF.udf_id
IN (10001, 10002, 10003, 10004, 10005,
10006, 10007, 10008, 10009)-- add to the list
END;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources