Transact SQL conditional Select
-
Hi Everybody :) , How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL? The execution of the 2nd procedure is conditional and based on whether the 1st statement returns any values or not? Essentially as an example: select * from cupboard where cupColour = 'white' if cupboard.CupsFound = 0 then select * from cupboard end if return dataset Any help would be greatly appreciated...i will continue to search and read :)
-
Hi Everybody :) , How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL? The execution of the 2nd procedure is conditional and based on whether the 1st statement returns any values or not? Essentially as an example: select * from cupboard where cupColour = 'white' if cupboard.CupsFound = 0 then select * from cupboard end if return dataset Any help would be greatly appreciated...i will continue to search and read :)
-
butchzn wrote:
How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL?
One at a time :) Try an
IF EXISTS()
BEGINEND
I are Troll :suss:
Hi Thanks for bumping me in the right direction i have managed to solve it with your input! ALTER PROC [dbo].[FindCupByCupIdAndDescription] @CupID int, @CupDescription nvarchar(50) AS IF EXISTS(SELECT CupID FROM Cupboard WHERE CupId = @CupId) SELECT * FROM Cupboard WHERE CupId = @CupId ELSE SELECT * FROM Attic WHERE CupDescription LIKE '%' + @CupDescription + '%' this is a really simplified example but is essentially the logical illustration of what i was hoping to achieve using a result as a condition. :-D Thanks you.
-
Hi Thanks for bumping me in the right direction i have managed to solve it with your input! ALTER PROC [dbo].[FindCupByCupIdAndDescription] @CupID int, @CupDescription nvarchar(50) AS IF EXISTS(SELECT CupID FROM Cupboard WHERE CupId = @CupId) SELECT * FROM Cupboard WHERE CupId = @CupId ELSE SELECT * FROM Attic WHERE CupDescription LIKE '%' + @CupDescription + '%' this is a really simplified example but is essentially the logical illustration of what i was hoping to achieve using a result as a condition. :-D Thanks you.
-
Hi Thanks for bumping me in the right direction i have managed to solve it with your input! ALTER PROC [dbo].[FindCupByCupIdAndDescription] @CupID int, @CupDescription nvarchar(50) AS IF EXISTS(SELECT CupID FROM Cupboard WHERE CupId = @CupId) SELECT * FROM Cupboard WHERE CupId = @CupId ELSE SELECT * FROM Attic WHERE CupDescription LIKE '%' + @CupDescription + '%' this is a really simplified example but is essentially the logical illustration of what i was hoping to achieve using a result as a condition. :-D Thanks you.
Assuming that you want to use either ID or description and not both (does not make sense) then the following will do the job with a conditional where caluse.
SELECT *
FROM TableName
WHERE (ISNULL(@CupID,0)=0 OR CupID = @CupID)
AND (ISNULL(@CupDesc,'') = '' OR CupDesc = @Cupdesc)There are a number of variations on this, search for "conditional where clause" Caveat, string too many of these together (5+) and SQL Server gets very cranky
Never underestimate the power of human stupidity RAH
-
Hi Everybody :) , How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL? The execution of the 2nd procedure is conditional and based on whether the 1st statement returns any values or not? Essentially as an example: select * from cupboard where cupColour = 'white' if cupboard.CupsFound = 0 then select * from cupboard end if return dataset Any help would be greatly appreciated...i will continue to search and read :)
SELECT T2.* FROM (SELECT TOP(1) Color FROM Cabinet WHERE Color='Yellow') T1 INNER JOIN Cabinet T2 ON 0=0 :-D