July 30, 2008 at 12:49 pm
ok, I am having a major brain fart today and it is driving me crazy~
The idea here is that I need to select some data straight from the table and then depending on if the passed in parameter is CURRENT or HOME do this or that......
My problem is I got the if/else statements to work outside the stored procedure the way I want it (this is a subset of the if/else). Now I need to incorporate it into the SELECT statement of the sp. I cannot for the life of me figure out where to put this or IF I can do it this way......
Can you please send me some guidance OH WISER ONES???????????????????????? thank you in advance!!
snippet:
SELECT
u.Unit
, e.EquipmentDesc
, hl.RegionAbbrev + ',' + hl.LocName AS HomeLocation
, pl.RegionAbbrev + ',' + pl.LocName AS CurrentLocation
IF @LocationType = 'CURRENT' and @LocationSearchString = ''
BEGIN
(SELECT
pl.LocationID AS PLocID
, pl.RegionAbbreviation AS PRegionAbbrev
, pl.LocationName AS PLocName
FROM dbo.Location pl
INNER JOIN dbo.Unit u ON pl.LocationID = u.PresentLocationID
)
END
else
if @LocationType = 'HOME' and @LocationSearchString = ''
BEGIN
(select
hl.LocationID AS HLocID
, hl.RegionAbbreviation AS HRegionAbbrev
, hl.LocationName AS HLocName
from dboLocation hl
INNER JOIN dbo.Unit u on hl.LocationID = u.HomeLocationID
)
END
FROM
dbo.Unit u
dbo.Equipment e
July 30, 2008 at 12:56 pm
Are you trying to build up the select statement with IFs? Can a CASE work
CASE when @home = home
then a.xxx
Or are you trying to build dynamic SQL? Honestly I'd write 3 procs (or however many) and call the appropriate one based on parameters.
July 30, 2008 at 1:05 pm
no dynamic SQL...
what I need is data that I am pulling straight from the table and then based on 2 parameters data about the location. they will pass in HOME or CURRENT and then '' (nothing) or string. so based on what they pass in is how I need to build the Location string to return in the select statement.
Now what you said about the CASE statement....can I do a case like this:
CASE
when @locationType = 'current' and @locationsearchstring = '' then
(select pl.locationid from dbo.Location where blah blah blah)
or how do I do it??
Geez...I feel so lost on this one!! Thanks for the help!
July 30, 2008 at 1:12 pm
I think this would be easier if we had the DDL for the table (CREATE statement), sample data (in the form of INSERT statements so we can cut, paste, and execute to load the table), the code you currently have (the snippet you posted looks like it is missing some code), and the expected output (in this case several examples based on possible inputs) for checking results.
😎
July 30, 2008 at 3:18 pm
Angelindiego (7/30/2008)
no dynamic SQL...what I need is data that I am pulling straight from the table and then based on 2 parameters data about the location. they will pass in HOME or CURRENT and then '' (nothing) or string. so based on what they pass in is how I need to build the Location string to return in the select statement.
Now what you said about the CASE statement....can I do a case like this:
CASE
when @locationType = 'current' and @locationsearchstring = '' then
(select pl.locationid from dbo.Location where blah blah blah)
or how do I do it??
Geez...I feel so lost on this one!! Thanks for the help!
For additional help (or better/faster help), please read the following:
Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/
You should also review CASE in BOL, as what you are looking for is the searched case expression. That is:
CASE WHEN [expression] THEN [condition]
WHEN [other expression] THEN [condition]
ELSE [condition]
END
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2008 at 3:43 pm
BOL is very unclear about being able to do weird stuff with a case in this case (no pun intended) being able to do a select within the case. I couldn't find anything on this site as well, thus the reason I am asking you pros.
I will keep trying and researching...thank you for you help.
July 30, 2008 at 3:55 pm
Angelindiego (7/30/2008)
BOL is very unclear about being able to do weird stuff with a case in this case (no pun intended) being able to do a select within the case. I couldn't find anything on this site as well, thus the reason I am asking you pros.I will keep trying and researching...thank you for you help.
Yes, you can use queries for the expression or the condition.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2008 at 3:58 pm
July 30, 2008 at 7:36 pm
Angelindiego,
If done incorrectly, your CASE statement will turn out to be RBAR on steroids in the form of a correlated subquery. If you want, please post your final code and let's have a look-see to be sure one way of the other. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2008 at 8:04 am
July 31, 2008 at 1:28 pm
ok, I am trying to go about this another direction....my thoughts are to find out what the data is I need and apply it to a variable and then use the variable in the string I am concatenating for the location. so...I would like to do something like this, but it doens't work.
IF @LocationType = 'CURRENT' and @LocationSearchString = ''
BEGIN
(SELECT
pl.LocationID AS PLocID
----->, @PRegionAbbrev = pl.RegionAbbreviation
----->, @PLocName = pl.LocationName
FROM dbo.Location pl
INNER JOIN dbo.Unit u ON pl.LocationID = u.PresentLocationID
)
END
I got an error that says I can't set variable in a data retrieval situation
So then I tried this:
IF @LocationType = 'CURRENT' and @LocationSearchString = ''
BEGIN
(SELECT
pl.LocationID AS PLocID
, pl.RegionAbbreviation
, pl.LocationName
FROM org..Location pl
INNER JOIN Org..Unit u ON pl.LocationID = u.PresentLocationID
)
----> SET @PRegionAbbrev = pl.RegionAbbreviation
---->SET @PLocName = pl.LocationName
END
and I got an error that says:The column prefix 'pl' does not match with a table name or alias name used in the query.
any ideas or direction for me?? thank you in advance.....
July 31, 2008 at 4:10 pm
Should it be something like this?
SELECT {WHATEVER}
FROM dbo.Unit u
INNER JOIN dbo.Equipment e ON ???
INNER JOIN dbo.Location pl
ON pl.LocationID =
CASE @LocationType
WHEN 'CURRENT' THEN u.PresentLocationID
WHEN 'HOME' THEN u.HomeLocationID
END
_____________
Code for TallyGenerator
July 31, 2008 at 4:35 pm
Angelindiego (7/31/2008)
IF @LocationType = 'CURRENT' and @LocationSearchString = ''BEGIN
(SELECT
pl.LocationID AS PLocID
----->, @PRegionAbbrev = pl.RegionAbbreviation
----->, @PLocName = pl.LocationName
FROM dbo.Location pl
INNER JOIN dbo.Unit u ON pl.LocationID = u.PresentLocationID
)
END
I got an error that says I can't set variable in a data retrieval situation
This should be:
IF @LocationType = 'CURRENT' and @LocationSearchString = ''
BEGIN
(SELECT @PRegionAbbrev = pl.RegionAbbreviation
, @PLocName = pl.LocationName
FROM dbo.Location pl
INNER JOIN dbo.Unit u ON pl.LocationID = u.PresentLocationID
)
END
You can't return data and set variables in the same select statement. Whether or not you do this in a case expression or in the above, you need to make sure you are returning only one row. If the query can return more than one row - you won't know which values are going to be set.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 31, 2008 at 4:47 pm
I guess I've got it wrong 1st time.
This should be closer:
SELECT
u.Unit
, e.EquipmentDesc
, hl.RegionAbbreviation + ',' + hl.LocationName AS HomeLocation
, pl.RegionAbbreviation + ',' + pl.LocationName AS CurrentLocation
FROM dbo.Unit u
INNER JOIN dbo.Equipment e ON ??? -- not clear from youe query
LEFT JOIN dbo.Location pl ON pl.LocationID = u.PresentLocationID
LEFT JOIN dbo.Location hl ON pl.LocationID = u.HomeLocationID
_____________
Code for TallyGenerator
August 1, 2008 at 10:10 am
thanks you guys for all the input, I so appreciate it. What I have done and so far it is working, is that I have several if statements and they populate a temp table. Then in my query, I am joining to the temp table. so far so good....now I just have to finish and see what the execution plan shows!
Have a great weekend and thanks again!!!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply