Dynamic query

Question

Dynamic query

piu.scorpio

Old Hand

Points: 353
More actions
April 2, 2019 at 7:58 am

#2475739

Hello!
I have a query where I am declaring two context variables (@projectId, @version) in the job and using those variables to run the query. The condition I am running on is if you find the value in the context variable then run with that value and if null then run for all data in the table. My query works when the context variables are filled but it shows no results when the context variables are null. I am not able to understand what is wrong in my query. My query is like:
Declare @projectId int;
Declare @version varchar(20);
set @projectId = " + context.projectId + ";
set @version = '" + context.version + "';
select *
from SA.VAP_RESOURCE r
where ETL_ACTIVE_FLG = 1 and BUDGET_FLAG = 0
and PROJECT_ID = case when @projectId is null then PROJECT_ID else @projectId end
and CALCULATION_VERSION = case when @version is null then CALCULATION_VERSION else (''+ @version +'') end
Regards
Priya

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Does that SQL work? I suspect it doesn't, as context (ironically) has no context in the 2 lines below:

SET @projectId = '' + context.projectId + '';
SET @version = '' + context.version + '';

Also, if @projectId is an int, why are you adding blank strings to it? '' would be implicitily cast to an int, which would be 0. So your expression becomes:

SET @projectId = 0 + context.projectId + 0;

Which seems like quite odd logic.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Try a slightly simplified and perhaps more common version of your "catch-all" query:

SELECT *

FROM SA.VAP_RESOURCE r

WHERE r.ETL_ACTIVE_FLG = 1

AND r.BUDGET_FLAG = 0

AND (@projectId IS NULL OR r.PROJECT_ID = @projectId)

AND (@version IS NULL OR r.CALCULATION_VERSION = @version)

This reply was modified 5 years, 8 months ago by ChrisM@Work.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

piu.scorpio Old Hand Points: 353 More actions · Answer 3

Hi Chris

Thank you for your reply. But I have an issue. Your suggestion works if i the local variable values are null. But in the actual job they come as blank not null. so the condition fails. I tried to force the NULL value if the context variable is blank. But there is some code error. What can i use instead of isempty or isblank? here is what i am trying to achieve:

set @version = '" + context.version + "';

set @version = case when isempty(@version) then null else @version;

Regards

Priya

Thom A SSC Guru Points: 99130 More actions · Answer 4

piu.scorpio wrote:

Hi Chris Thank you for your reply. But I have an issue. Your suggestion works if i the local variable values are null. But in the actual job they come as blank not null. so the condition fails. I tried to force the NULL value if the context variable is blank. But there is some code error. What can i use instead of isempty or isblank? here is what i am trying to achieve: set @version = ‘” + context.version + “‘; set @version = case when isempty(@version) then null else @version; Regards Priya

Can you explain this? Like I said in my reply above, that won't work. Also isempty isn't a SQL Server function. Are you actually using SQL Server here?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

piu.scorpio Old Hand Points: 353 More actions · Answer 5

Hi Thom

Yes. I am using sql but couldnt remember the corresponding function for isempty. So asked the question. But with some search i found that i can use nullif() to have null value when the variable is blank/empty.

Regards

Priya

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Have you posted the full code you are working with or are you simplifying what you are working with? If the latter, you are losing much in the translation. It would help if you posted the actual code you are working with.

piu.scorpio Old Hand Points: 353 More actions · Answer 7

Hello!

The issue is solved now. I took Chris's solution as a reference and it solved the problem. Thank you all for your help! 🙂

Regards

Priya