December 4, 2015 at 1:16 am
We have a query that runs and activates a print in our system. My colleague want it to only activate if a parameter we select contains 1 in it.
Can anyone here point me in the right direction on how to set it up?
It basically need to cancel the query if the parameter contains 0. I assume we should use CASE for this but im bit unsure how since i dont use SQL that often. I just used SQL for simple selects and update in the past.
Thanks in advance for any help
December 4, 2015 at 1:48 am
Probably something like
IF @Parameter=1
SELECT...
but that's guessing without seeing the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2015 at 1:58 am
its basically like this:
select * FROM
(
select
m.something,
m.something,
m.something,
substring(p.something,1,30) something,
m.something,
p.trigger
from something m with(NOLOCK)
left outer join something p with(NOLOCK) on p.something=m.something
) TV
Maybe i can just use a WHERE on p.trigger at the end ?
December 4, 2015 at 2:08 am
I meant the whole batch of code, not a single select.
Firstly, ditch the NOLOCK hints, they're not go-faster switches, they're essentially WITH (RETURNPOTENTIALLYINCORRECTDATA) hints. If you don't mind the results being wrong part of the time, leave them in.
Now, precisely what do you want to happen?
Return only the rows where p.trigger = 1?
Return nothing if there's any row with p.trigger = 1?
Not run the query at all if there's any row with p.trigger = 1?
Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2015 at 2:21 am
GilaMonster (12/4/2015)
I meant the whole batch of code, not a single select.Firstly, ditch the NOLOCK hints, they're not go-faster switches, they're essentially WITH (RETURNPOTENTIALLYINCORRECTDATA) hints. If you don't mind the results being wrong part of the time, leave them in.
Now, precisely what do you want to happen?
Return only the rows where p.trigger = 1?
Return nothing if there's any row with p.trigger = 1?
Not run the query at all if there's any row with p.trigger = 1?
Something else?
Ok let me explain a bit deeper. Basically this is a query used in fastreport (software that make reports and labels) . we import this reportfile into our system. When we press print a certain place the system uses this query to print the data from the query.
What i want to happen is if a.trigger equals 0 then this query wont execute, and the print does not execute. If a.trigger equals 1, the print executes.
(Thanks for trying to help)
December 4, 2015 at 2:27 am
Still too vague.
What happens if there are multiple rows with trigger = 1 and multiple rows with trigger = 0?
When should the print not happen? When all rows have trigger = 0? When any one has trigger = 0?
Something else?
Please post the current code with the print.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2015 at 2:41 am
GilaMonster (12/4/2015)
Still too vague.What happens if there are multiple rows with trigger = 1 and multiple rows with trigger = 0?
When should the print not happen? When all rows have trigger = 0? When any one has trigger = 0?
Something else?
Please post the current code with the print.
Sorry i hoped that was enough.
"
select * FROM
(
select
m.missionid,
m.extproductid,
m.actquantity,
substring(p.producttext,1,30) producttext,
m.extorderid,
from mission m with(NOLOCK)
left outer join product p with(NOLOCK) on p.extproductid=m.extproductid
) TV
--{WHERE}--
"
Thats the whole code. Its been in use for a long time now by our customers, but now they want it to only execute if a coloum named p.activitycode equals 1. So i basically need to check if p.acitvitycode equals 1 before this query is run to the system. As it is now every time this query is used they get a print.
p.activitycode is the trigger i mentioned in my posts before
December 4, 2015 at 2:47 am
To clarify a bit more for you. They system uses a inparameter so it only gets one result. The query in practice will end up looking like this:
select * FROM
(
select
m.missionid,
m.extproductid,
m.actquantity,
substring(p.producttext,1,30) producttext,
m.extorderid,
from mission m with(NOLOCK)
left outer join product p with(NOLOCK) on p.extproductid=m.extproductid
where = m.missionid = 123445
) TV
--{WHERE}--
"
December 4, 2015 at 2:48 am
Ok, so what then, do you want to run the query and return an empty resultset? Not run the query at all?
And ditch those NOLOCK hints!
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2015 at 2:50 am
GilaMonster (12/4/2015)
Ok, so what then, do you want to run the query and return an empty resultset? Not run the query at all?And ditch those NOLOCK hints!
Not run the query at all would be ideal.
I will. i will. 🙂
December 4, 2015 at 3:03 am
Is the column in the mission table or product? Is it just called "trigger"?
If it's in Product, there's a chance it could be NULL due to the outer join. If it's NULL, does that mean run query or not run query?
Is it basically, column = 1 run query, column = anything else (including NULL), don't run query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2015 at 3:18 am
GilaMonster (12/4/2015)
Is the column in the mission table or product? Is it just called "trigger"?If it's in Product, there's a chance it could be NULL due to the outer join. If it's NULL, does that mean run query or not run query?
Is it basically, column = 1 run query, column = anything else (including NULL), don't run query?
The name for my "trigger" is PRODUCT.ACTIVITYCODE.
Yes basically, p.ACTIVITYCODE = 1 run query, column = anything else (including NULL), don't run query. 🙂
December 4, 2015 at 3:31 am
IF (SELECT p.ActivityCode FROM mission m LEFT OUTER JOIN product p ON p.extproductid = m.extproductid WHERE m.missionid = 12344) = 1
BEGIN
-- query to be selectively run goes here
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2015 at 3:40 am
GilaMonster (12/4/2015)
IF (SELECT p.ActivityCode FROM mission m LEFT OUTER JOIN product p ON p.extproductid = m.extproductid WHERE m.missionid = 12344) = 1BEGIN
-- query to be selectively run goes here
END
Thanks that work perfectly in sql server. One problem though is that the sentence :
WHERE m.missionid = 4095514) = '1'
is a in parameter from the system so i cant actually have it in my sentence. It needs to be at then end/after the query.
Example original query :
select * FROM
(
select
m.missionid,
m.extproductid,
m.actquantity,
substring(p.producttext,1,30) producttext,
m.extorderid,
from mission m with(NOLOCK)
left outer join product p with(NOLOCK) on p.extproductid=m.extproductid
) TV
--{WHERE}--
Original query with inparameter from system
select * FROM
(
select
m.missionid,
m.extproductid,
m.actquantity,
substring(p.producttext,1,30) producttext,
m.extorderid,
from mission m with(NOLOCK)
left outer join product p with(NOLOCK) on p.extproductid=m.extproductid
) TV
--{WHERE}--
where missionid = 4095514
December 4, 2015 at 3:54 am
??
If it's a parameter, then it's passed to the batch/procedure from the app, so you would just reference the parameter name, rather than the hardcoded value that I put as an example.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply