?
Solved

Stored Procedure - Test column type and JOIN

Posted on 2012-09-03
4
Medium Priority
?
268 Views
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.

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

0
Comment
Question by:swgdesign
  • 3
4 Comments
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 38361882
SELECT ... WHERE ... AND <column is string>
UNION ALL
SELECT ... WHERE ... AND <column is not string>
0
 

Author Comment

by:swgdesign
ID: 38369120
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
 

Accepted Solution

by:
swgdesign earned 0 total points
ID: 38548731
IN the end I changed my data structure and html elements to make the binding easier.
0
 

Author Closing Comment

by:swgdesign
ID: 38564894
Solved the solution my own way with a new table structure as the union didn't make sense
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 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