Stored Procedure - Test column type and JOIN

I have the following SPROC where 2 joins are made on c.Sire and c.Dam. My problem is that these 2 columns could either contain a number or a string. If a number, I need the two joins to occur as there will be a related record in the RV_Animals table. If the column value is a string I don't want to do the joins as there will be related record.

SELECT 
	c.ClutchID, c.GUID, c.UserID, c.CustomClutchID, c.ClutchType, c.Species, sp.SpeciesName, c.Sire, sr.CustomAnimalID, c.SireMorph, c.SirePhoto, c.Dam, dm.CustomAnimalID, c.DamMorph, c.DamPhoto, c.ClutchLaid,
	c.ClutchBorn, c.ClutchSize, c.ClutchOK, c.ClutchSlugs, c.Notes, c.Live, c.Deleted, c.DateAdded, c.DateModified 
	FROM RV_Clutches c 
	LEFT JOIN RV_Species sp on sp.SpeciesID = c.Species	
	LEFT JOIN RV_Animals sr ON sr.AnimalID = c.Sire 
	LEFT JOIN RV_Animals dm on dm.AnimalID = c.Dam
	WHERE
	(@ClutchID = -1 OR ClutchID = @ClutchID) AND
	(@UserID = -1 OR c.UserID = @UserID) AND
	(@CustomClutchID = '' OR CustomClutchID = @CustomClutchID) AND
	(@ClutchType = '' OR ClutchType = @ClutchType) AND 
	(@Species = -1 or SpeciesID = @Species) AND
	(@GUID = '' OR c.GUID = @GUID)  AND
	(@Deleted = -1 OR c.Deleted= @Deleted) 
	ORDER BY c.DateModified DESC

Open in new window

swgdesignAsked:
Who is Participating?
 
swgdesignAuthor Commented:
IN the end I changed my data structure and html elements to make the binding easier.
0
 
Andrei FomitchevCommented:
SELECT ... WHERE ... AND <column is string>
UNION ALL
SELECT ... WHERE ... AND <column is not string>
0
 
swgdesignAuthor Commented:
OK, but in relation to my sproc how does that into it? I also don't see how that would do the job either?
0
 
swgdesignAuthor Commented:
Solved the solution my own way with a new table structure as the union didn't make sense
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.