Stored Procedure - Test column type and JOIN

Posted on 2012-09-03
Last Modified: 2012-11-04
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.

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

Question by:swgdesign
    LVL 8

    Expert Comment

    by:Andrei Fomitchev
    SELECT ... WHERE ... AND <column is string>
    SELECT ... WHERE ... AND <column is not string>

    Author Comment

    OK, but in relation to my sproc how does that into it? I also don't see how that would do the job either?

    Accepted Solution

    IN the end I changed my data structure and html elements to make the binding easier.

    Author Closing Comment

    Solved the solution my own way with a new table structure as the union didn't make sense

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now