Quick question about T-SQL and parameters

[Captain]

Is there a sensible way to only use a parameter if it's defined by the caller?

Here's what's up:

I have a query that returns about two hundred million rows. Each row has an ID that I could be searching on.

Coincidentally, if I search on that ID, the query is logically identically to a different query I wrote and have tests written for. (I.e., today's query is like I looped through a table and did an abstract "join" on my old query)

OK, so can I somehow do something like:

declare @InvestigationId as Int;

... -- bunch of ctes and temp tables, etc

select *
  from AllInvolvement
where (@InvestigationId is null or AllInvolvement.InvestigationId = @InvestigationId)

If so, how? The naive thing isn't working.

[lolwhat]

Really, the best way I know is to break it up:

if @InvestigationId is null
begin
   select * from AllInvolvement;
end;
else
begin
   select * from AllInvolvement where AllInvolvement.InvestigationId = @InvestigationId;
end;

[Unperverted Vixen]

@Captain said in Quick question about T-SQL and parameters:

If so, how? The naive thing isn't working.

The naive thing worked when I tried it.

[Groaner]

@Captain said in Quick question about T-SQL and parameters:

Is there a sensible way to only use a parameter if it's defined by the caller?

Define "defined." I could do this...

exec myProc @InvestigationID = null
...

... and the callee would not be able to detect that the caller had defined the parameter, just that its value was null.

Here's what's up:

I have a query that returns about two hundred million rows. Each row has an ID that I could be searching on.

Coincidentally, if I search on that ID, the query is logically identically to a different query I wrote and have tests written for. (I.e., today's query is like I looped through a table and did an abstract "join" on my old query)

OK, so can I somehow do something like:

declare @InvestigationId as Int;

... -- bunch of ctes and temp tables, etc

select *
  from AllInvolvement
where (@InvestigationId is null or AllInvolvement.InvestigationId = @InvestigationId)

If so, how? The naive thing isn't working.

What you have here is a common pattern for "either get this one row or the whole table" and aside from parameter sniffing and performance considerations, ought to work just fine. Unless your intent is something different?

[error]

@Groaner said in Quick question about T-SQL and parameters:

and the callee would not be able to detect that the caller had defined the parameter, just that its value was null.

Fun note: JavaScript has undefined, and if you don't define the parameter or property, it will be undefined (rather than null)... but it might be explicitly specified as undefined, so there is a third, rarely handled case, that involves checking the parameter count, or hasOwnProperty for options objects.

Overall, the distinction between null and undefined is mostly more trouble than it's worth.

[lolwhat]

@Captain said in Quick question about T-SQL and parameters:

where (@InvestigationId is null or AllInvolvement.InvestigationId = @InvestigationId)

Oh, and logical operations don't short-circuit in T-SQL. So, both conditions will be evaluated... perhaps for every row.

[error]

@lolwhat said in Quick question about T-SQL and parameters:

Oh, and logical operations don't short-circuit in T-SQL. So, both conditions will be evaluated... perhaps for every row.

I've seen a pattern like where 1 = case when @Foo is null then 1 when [condition] then 1 else 0 end.

Not sure if this helps here.

[Groaner]

@lolwhat said in Quick question about T-SQL and parameters:

@Captain said in Quick question about T-SQL and parameters:

where (@InvestigationId is null or AllInvolvement.InvestigationId = @InvestigationId)

Oh, and logical operations don't short-circuit in T-SQL. So, both conditions will be evaluated... perhaps for every row.

Right, table scans are a risk, but can be mitigated somewhat depending on what kinds of search conditions and indexes are being employed. It's also a question of how the schema is set up - a table scan against a tiny table may be no big deal.

If it helps, might be worth checking out a decade-old writeup on dynamic search conditions that was state-of-the-art when I entered the industry but seems to be updated with notes on more recent versions.

[Groaner]

@error said in Quick question about T-SQL and parameters:

@lolwhat said in Quick question about T-SQL and parameters:

Oh, and logical operations don't short-circuit in T-SQL. So, both conditions will be evaluated... perhaps for every row.

I've seen a pattern like where 1 = case when @Foo is null then 1 when [condition] then 1 else 0 end.

Not sure if this helps here.

On the surface, that might be less index-friendly, but who knows - the query optimizer might see through it? The only thing we can really do is see what query plan comes of it and tune accordingly.

[Zecc]

@Unperverted-Vixen said in Quick question about T-SQL and parameters:

@Captain said in Quick question about T-SQL and parameters:

If so, how? The naive thing isn't working.

The naive thing worked when I tried it.

I wrote something similar myself days ago. Worked for me.

A decade-old writeup on dynamic search conditions that was state-of-the-art when @Groaner entered the industry but seems to be updated with notes on more recent versions said:

The effect of all the @x IS NULL clauses is that if an input parameter is NULL, then the corresponding ANDcondition is always true. Thus, the only conditions that are in effect are those where the search parameter has a non-NULL value. Sounds simple enough, but there is a very big difference in performance with or without that last line present:

OPTION (RECOMPILE)

Interesting. I might try that. The test database I was given is somewhat small, so I'm not sure I'll be able to truly assess the impact, but at least I can look at the plan.

[djls45]

@error said in Quick question about T-SQL and parameters:

@Groaner said in Quick question about T-SQL and parameters:

and the callee would not be able to detect that the caller had defined the parameter, just that its value was null.

Fun note: JavaScript has undefined, and if you don't define the parameter or property, it will be undefined (rather than null)... but it might be explicitly specified as undefined, so there is a third, rarely handled case, that involves checking the parameter count, or hasOwnProperty for options objects.

Overall, the distinction between null and undefined is mostly more trouble than it's worth.

In SQL, undefined either is a syntax error (the thing wasn't declared) or is indistinguishable from null.

[Tsaukpaetra]

@djls45 said in Quick question about T-SQL and parameters:

@error said in Quick question about T-SQL and parameters:

@Groaner said in Quick question about T-SQL and parameters:

and the callee would not be able to detect that the caller had defined the parameter, just that its value was null.

Fun note: JavaScript has undefined, and if you don't define the parameter or property, it will be undefined (rather than null)... but it might be explicitly specified as undefined, so there is a third, rarely handled case, that involves checking the parameter count, or hasOwnProperty for options objects.

Overall, the distinction between null and undefined is mostly more trouble than it's worth.

In SQL, undefined either is a syntax error (the thing wasn't declared) or is indistinguishable from nullwhatever operation you're trying to do, the answer is false.

FTFY.

[error]

@Tsaukpaetra said in Quick question about T-SQL and parameters:

@djls45 said in Quick question about T-SQL and parameters:

@error said in Quick question about T-SQL and parameters:

@Groaner said in Quick question about T-SQL and parameters:

and the callee would not be able to detect that the caller had defined the parameter, just that its value was null.

Fun note: JavaScript has undefined, and if you don't define the parameter or property, it will be undefined (rather than null)... but it might be explicitly specified as undefined, so there is a third, rarely handled case, that involves checking the parameter count, or hasOwnProperty for options objects.

Overall, the distinction between null and undefined is mostly more trouble than it's worth.

In SQL, undefined either is a syntax error (the thing wasn't declared) or is indistinguishable from nullwhatever operation you're trying to do, the answer is false.

FTFY.

I find it helpful to think of "null" as "I dunno."

"Is 2 equal to I dunno? I dunno."
"Is 2 not equal to I dunno? I dunno."

[izzion]

@Zecc @Captain

@Zecc said in Quick question about T-SQL and parameters:

The effect of all the @x IS NULL clauses is that if an input parameter is NULL, then the corresponding ANDcondition is always true. Thus, the only conditions that are in effect are those where the search parameter has a non-NULL value. Sounds simple enough, but there is a very big difference in performance with or without that last line present:
OPTION (RECOMPILE)

Interesting. I might try that. The test database I was given is somewhat small, so I'm not sure I'll be able to truly assess the impact, but at least I can look at the plan.

In context of the original question... if you want the caller to not be required to define the parameter, you can just set the default value to NULL, such as:

DECLARE @InvestigationId AS INT = NULL;

And then the naive approach in the OP will work.

That said, you'll have also introduced a Parameter Sniffing problem into your procedure - depending on what parameter comes through to build the plan, other parameters may not perform reliably. The simplest scenario would be that if the plan compiles with a parameter that returns exactly 1 row, and then someone executes the procedure with NULL, the NULL case is probably going to loop row-by-row (RBAR in DBA speak), and completely blow up your server while it executes. Conversely, if the NULL case comes through first, then someone executes the procedure with the ID value that returns exactly one row, the one row case will use a TON more memory (since it's working with a plan that assumes the whole table) and take slightly longer than it would have with the optimized plan.

If you do hit that parameter sniffing problem, you have a few basic paths forward. I'm listing these paths in descending order of SQL Performance optimization value -- what the business optimization value of each case is will vary based on your environment.

1. Have two separate stored procedures in SQL code (one for the NULL case, one for the parameterized case), and have your application code determine which procedure to call. This can still have some parameter sniffing problems in the parameterized case, if one parameter generates just a few rows while another parameter generates a significant chunk of the table, but you can then use some of the other techniques below specifically for the parameterized case. This is the "best" option from a performance standpoint, since web/application CPU time is a lot cheaper than SQL CPU time - make the application do the thinking for what code branch needs to run.

2. If changing the application code isn't an option, have the SQL procedure generate dynamic SQL (or call two different procedures dynamically within the "master" procedure, based on what parameter came in). Two separate procedures is in my opinion a little cleaner:

CREATE PROCEDURE master_procedure
    @InvestigationId INT = NULL
AS
BEGIN
    IF @InvestigationId IS NULL
        EXEC sp_query_no_parameter
    ELSE
        EXEC sp_query_with_parameter @InvestigationId
END

and then you'd have the two sub-procedures that are "duplicated" code, except for the WHERE clause. Of course, then you get into duplicated code territory, so the other option is to have the master procedure generate and execute dynamic SQL:

CREATE PROCEDURE master_procedure
    @InvestigationId INT = NULL
AS
BEGIN
    DECLARE @Query NVARCHAR(MAX)
    @Query = N'SELECT * FROM AllInvolvement WHERE 1=1'
    IF @InvestigationId IS NULL
        EXEC sp_executesql @Query
    ELSE
    BEGIN
        @Query += ' AND AllInvolvement.InvestigationId = @Id'
        EXEC sp_executesql @Query, @params = N'@Id INT', @Id = @InvestigationId
    END
END

(alternately, you can just string-concatenate the original parameter value directly into the query and not re-parameterize, since the original parameterization should prevent SQL injection... but re-parameterizing helps the query planner a bit and is just generally a safer approach). Either of the two above approaches give you the separate plans for NULL vs not-NULL.

3. Assuming that neither of the other two approaches are allowable for a business case, the next option is to "hint" to SQL which plan it should use. If the executing the NULL case with a not-NULL plan blows up, but executing the not-NULL case with a NULL plan works within the acceptable return time (and the memory & I/O demand isn't too crazy), you could tell SQL "always compile this plan assuming the parameter is NULL" with an OPTIMIZE FOR hint

CREATE PROCEDURE master_procedure
    @InvestigationId INT = NULL
AS
BEGIN
    SELECT *
    FROM AllInvolvement
    WHERE @InvestigationId IS NULL
        OR AllInvolvement.InvestigationId = @InvestigationId
    OPTIMIZE FOR (@InvestigationId = NULL)
END

4. If code changes or dynamic SQL aren't viable, and the NULL vs not-NULL plans (or the various possible not-NULL plans, of a few rows or a bunch of rows) are too different so that no one plan works well enough for the other case(s), you can slap OPTION (RECOMPILE) at the end of the query, to tell SQL "don't cache a plan for this query, always determine the best plan at run-time based on the parameter value you see on that specific run". This has a CPU / performance penalty, since SQL will spend CPU time to determine the best plan every time the procedure is executed, instead of caching a plan like normal behavior. But, if the procedure isn't executed super frequently, and/or the difference in getting a new plan saves a lot more execution resources than it costs in compilation resources, then RECOMPILE can be a pretty good approach. In general, it's considered a "code smell", but sometimes it's the best way forward.

Usually, my approach is "if this is in production and on fire right now, slap a RECOMPILE on it and figure out which option 1-3 is the best long term solution". In the long term, I do look for the permanent fix in the general priority order above... but sometimes a grungy reporting query is just a grungy query that doesn't get hit very often, so it just gets a RECOMPILE and forget approach, since there's no payoff in tuning further.

[Captain]

@Groaner Yeah, I understand the pattern as far as the sql goes. But I guess I'm running into a problem, because the real code breaks when my key parameter isn't defined.

I don't understand it. I ran the query with a null parameter this morning, and got an empty result. I reset the DB connection and got a couple hundred million rows.

None of the temp tables are parametrized (at all), just indexed.

So count me as confused, but maybe happy, and getting ready to regression test this query against all the previous tests I wrote.

(Actually, I did explicitly set a variable to null between this morning and just now, when I implemented @lolwhat's advice, so it seems @izzion was right)

(Maybe I should do this kind of expansion on all the formulary queries I write... relatively easy to do, cheap to test, good data for BI)

I'm really not concerned about the parameter sniffing problem, because this query is only ever going to take in a parameter given by my regression testing harness. (The whole dataset is going to become a PowerBI dataset, eventually)

[Captain]

OK, new update.

This isn't working.

Maybe I should explain some more.

The query starts like...

declare @CpsId as Int = null;
declare @InvestigationId as Int = null;

if @CpsId is not null
  select @InvestigationId = ...
    from investigation invs ...
else
  set @InvestigationId = null;

-- ctes and stuff etc
...

if @InvestigationId is not null
  with AllInvolvement as (
            ...      
  )

  select *
    from AllInvolvement
   where AllInvolvement.InvestigationId = @InvestigationId;

else
  with AllInvolvement as (
    ...
  )

  select *
    from AllInvolvement;

OK. If I go into the query and manually set the CpsId to a specific value, the query returns 8 rows. If I run the corresponding regression tests (with the same CpsId), I get the whole table (and of course my test fails, because it was expecting 8 rows)

So "something" must be going wrong with parameter binding.

I'm using Dapper to call the query and bind parameters (wrapped inside the VS2013 test runner)

But the query will also be run directly (like in Visual Studio, or PowerBI), without editing, and without a CpsId parameter set (I want it to return the whole table by default).

So I'm not sure what I need.

[izzion]

@Captain
What happens if you change it to actually be a stored procedure?

CREATE PROCEDURE foo
    @CpsId INT = NULL
AS
BEGIN
    DECLARE @InvestigationId INT = NULL
    
    IF @CpsId IS NOT NULL
        SET @InvestigationId = (SELECT InvestigationId FROM Investigation WHERE ...)

    SELECT *
    FROM AllInvolvement
    WHERE @InvestigationId IS NULL
    OR AllInvolvement.InvestigationId = @InvestigationId
END

and then call EXEC foo 1 to execute the query for CpsId = 1 (you can also specify EXEC foo @CpsId=1 for readability if desired)

[Captain]

@izzion I don't know, I'll give that a try after lunch.

[izzion]

@Captain
Also, depending on how complicated the real query is, you might even be able to mash the translation from CpsId to InvestigationId into a JOIN (or a WHERE EXISTS, which is a little better if you're just joining for row elimination), and remove the need for the InvestigationId variable altogether. I'm assuming there's stuff getting lost in the psuedo-code and that's not really practical, but if it is, then you should go ahead and do the join so that SQL can optimize the two queries together:

CREATE PROCEDURE foo
    @CpsId INT = NULL
AS
BEGIN
    SELECT *
    FROM AllInvolvement ai
    WHERE EXISTS (
        SELECT 1
        FROM Investigation i
        WHERE i.InvestigationId = ai.InvestigationId
        AND (
            @CpsId IS NULL 
            OR i.CpsId = @CpsId
        )
    )
END

[djls45]

@Captain said in Quick question about T-SQL and parameters:

if @CpsId is not null
  select @InvestigationId = ...
    from investigation invs ...
else
  set @InvestigationId = null;

Maybe there's stuff missing, but in this part, doesn't the select @InvestigationId = ... only set @InvestigationId to the last value in the investigation table? So unless there is a where clause (that was snipped out) limiting to one specific row or you're only needing it for a null-ness check (which doesn't appear to be the case, since the value is used later on), that's going to give unexpected results.

[Captain]

@djls45 Sorry, my mistake, (thank you) -- should have left the @CpsId filter in. @InvestigationId is a PK on the table and @CpsId is a PK on its own table, so we should just get the one InvestigationId:

if @CpsId is not null
  select @InvestigationId = invs.ID_INVS
    from investigation invs ... 
      where invs.ID_CPS = @CpsId -- not actually the structure but close enough.
else
  set @InvestigationId = null;

[Captain]

OK, I made some more progress.

I wrote up my stored procedure, like @izzion said:

create procedure foo
  @CpsId int = null
as

begin
... -- all the stuff, including defining `AllInvolvement`.
end

insert into #AllInvolvement execute #spInvestigationDataGet;

drop procedure spInvestigationDataGet;

select *
  from #AllInvolvement;

So that looks okay until I run it in either VS or my test suite. If I run in VS, I get the lovely result

Command(s) completed successfully.

with zero rows in my result set... Definitely trying to google my way out of this problem, but somewhat stuck again anyway.

[izzion]

@Captain
Hm, well, now that changes a fair amount of my recommendation, if AllInvolvement is a temp table being populated at run time. Is there any way you can widen the psuedocode shot a little while still being sufficiently obfuscated to not get fired?

With the try you just did, was #AllInvolvement only defined within the SP, or did you also define it before the insert into #AllInvolvement execute #spInvestigationDataGet line and outside of the SP?

[Tsaukpaetra]

@Captain said in Quick question about T-SQL and parameters:

with zero rows in my result set...

Do a

SET NOCOUNT ON

At the top and then just before your final "results" select

SET NOCOUNT OFF

Most programs can't handle multiple results sets.

Edit: Although you did mention VS was returning nothing...

Wait, your create procedure is its own thing and shouldn't say anything more than what you got. You'll want to exec it for it to actually run now that you've created it.

[Captain]

This is anonymized, but is a good idea of what is going on in the current try. To answer your specific question, I defined #AllInvovlement in the SP, since I couldn't do it before the SP in the sql file.

create or alter procedure spDashbaordInvestigationParticipantsGet
   @CpsId int = null
as
begin

if object_id('tempdb..#FactByReportId') is null
  create table #FactByReportId ( ReportId bigint
                               , PersonId bigint
                               , FactCode char(1)
                               , FactStatement varchar(max)
                               , INDEX IX_TempFactsByReportId (ReportId, FactCode, PersonId)
                               )

  insert into #FactByReportId ( ReportId, FactCode, FactPersonId)
    select ... -- simple query returning big list of facts, basically building a cross ref table.
end

-- The following is a temporary cross reference table, with index, which gets used to filter out irrelevant people.
if object_id('tempdb..#InvestigationParticipants') is null
begin
  create table #Investigationparticipants ( InvestigationId bigint
                                          , ParticipantId bigint
                                          , INDEX IX_TempInvestigationParticipants (InvestigationId, ParticipantId)
                                          );
  insert into #InvestigationParticipants(InvestigationId, ParticipantId)
    select ...
      from Participants
      where Participants.Type = '4' -- DUN DUN DUN, a sign of things to come.
end

-- The first of the three "involvement" tables.
--
if object_id('tempdb..#ScreeningInvolvement') is null
begin
  create table #ScreeningInvolvement ( InvestigationId bigint
                                     , ParticipantId bigint
                                     , PriorReportId bigint
                                     , PriorReportType varchar(100)
                                     , ... -- more data fields
                                     , ... -- and an INDEX on InvestigationId and maybe a couple of other Id fields
                                     )
  select KeyInvestigation.InvestigationId
       , ScreeningReports.ScreeningReportId as PriorReportId
       , 'Screening Report' as PriorReportType

    into #ScreeningInvolvement
    from investigation KeyInvestigation inner join #InvestigationParticipants on #InvestigationParticipants.InvestigationId = KeyInvestigation.InvestigationId
                                        inner join Participants on Participants.ParticipantId = InvestigationParticipants.ParticipantId
                                                               and Participant.Type = '1' -- DUN DUN DUN screening involvement type of participant
                                        inner join ScreeningReports on ScreeningReports.ScreeningReportId = Participant.IntakeId -- the semantic type of the IntakeId is set by the Participant.Type
   where ... -- big filter, written in a style that makes my head hurt and has resisted all my refactoring attempts
end

-- Like I said, there are three of these.  Each of them are similar and suitable to be UNIONed,
-- but each look at the tables for the respective report types to get the data fields we pull in.

-- I'll skip writing them out.
if object_id('tempdb..#FssInvolvement') is null
begin
  create table #FssInvolvement ( InvestigationId bigint
                               , ...
                               )
end
if object_id('tempdb..#CpsInvolvement') is null
begin
  create table #CpsInvolvement ( InvestigationId bigint
                               , ...
                               )
end

if object_id('tempdb..#AllInvolvement') is null
  begin
    create table #AllInvolvement ( InvestigationId bigint
                                 , ReportDateTime datetime
                                 , PriorReportId bigint
                                 , CaseName varchar(max)
                                 , PerpetratorName varchar(max)
                                 , ReportDescription varchar(max)
                                 , ReportAllegation varchar(max)
                                 , Disposition varchar(max)
                                 );
  end;

with AllInvolvement as (
  (select * from #ScreeningInvolvement)
union all
  (select * from #CpsInvolvement)
union all
  (select * from #FssInvolvement)
)

select ai.*
  from AllInvolvement ai
 where exists ( select 1
                  from investigation invs inner join cps_report cps on cps.InvestigationId = invs.InvestigationId
                 where invs.InvestigationId = ai.InvestigationId
                   and ( @CpsId is null
                      or cps.CpsId = @CpsId
                       )
              );
end

insert into #AllInvolvement execute spDashboardInvestigationParticipantsGet;

drop procedure spDashboardInvestigationParticipantsGet;

select *
  from #AllInvolvement;

Before this round of tries, the involvement tables were ctes, and the query took like 12 hours. I got it down to like 5 - 15 minutes with the temp tables and indexes. This is only going to run like once a day, tops (and probably closer to once a month/quarter really)

[izzion]

@Captain said in Quick question about T-SQL and parameters:

Before this round of tries, the involvement tables were ctes, and the query took like 12 hours. I got it down to like 5 - 15 minutes with the temp tables and indexes. This is only going to run like once a day, tops (and probably closer to once a month/quarter really)

Ah. Sounds like you might have your 90% then, if the query is working correctly in that 5-15 minute approach. Let me know if you want to keep beating your head against this, and I'll try to take a deeper look after I get home from work.

[Captain]

@izzion said in Quick question about T-SQL and parameters:

Ah. Sounds like you might have your 90% then, if the query is working correctly in that 5-15 minute approach. Let me know if you want to keep beating your head against this, and I'll try to take a deeper look after I get home from wokr.

Yeah, but that query wasn't working because of how parameters are handled, some how, someway. So I tried SPs because they handle parameters differently, per advice on here. And yeah, the parameter handling seems to work, but now I can't/don't know how to get results out of the SP execution.

[izzion]

@Captain
Ah.

If you just create the stored procedure and call it from your code, it's not seeing the result set?

With the script, if you explicitly define #AllInvolvement after creating the procedure but before doing the INSERT INTO #AllInvolvement, do you get the expected result set within SSMS?

[Captain]

@izzion I changed it to...

create procedure
begin
  ...
end

if object_id('tempdb..#AllInvolvement') is null
  begin
    create table #AllInvolvement ( ... )
    insert into #AllInvolvement execute spDashboardInvestigationParticipantsGet;
end

select * from #AllInvolvement;

Still get "Command(s) completed successfully" and no results. :-(

[izzion]

@Captain
What version of SQL Server is this?

If you declare @CpsId as NULL and execute the body of the stored procedure (without creating a procedure), are you getting any results?

[Captain]

@izzion Oops, sorry...

It's version 14.0.3238. Doing the code transform now.

[Tsaukpaetra]

@izzion said in Quick question about T-SQL and parameters:

and call it from your code,

I'm not sure if this question was answered. Are you actually calling the procedure, or is your code doing the create procedure and expecting results?

[izzion]

@Captain
Hm. INSERT INTO ... EXECUTE is supported (for explicitly defined tables) in SQL 2017, so I would have expected that to work.

Can you try putting a GO after the END for the create procedure, and then changing the IF OBJECT_ID(... to:
DROP TABLE IF EXISTS #AllInvolvement
and then proceed with the CREATE & INSERT, without the BEGIN/END wrapper?

[Captain]

@Tsaukpaetra Thanks for checking. Yes, I am doing insert into #AllInvolvement execute spDashboardInvestigationParticipants. (But I didn't know how to do that yesterday before I asked, and did indeed expect the result set to get fired off as a side effect)

@izzion: I ran the query like you said:

-- create procedure
   declare @CpsId as Int = null;
-- as
begin
  ...
  select * from AllInvolvement -- the cte union
end

And yes, the query returned all the hundred million rows.

I'll try your next suggestion now.

[Captain]

@izzion The GO query worked in VS, but not in the C# test runner. Apparently GO isn't real T-SQL syntax, but taking it out of this try broke the query in VS again too.

[error]

@Captain said in Quick question about T-SQL and parameters:

@izzion The GO query worked in VS, but not in the C# test runner. Apparently GO isn't real T-SQL syntax, but taking it out of this try broke the query in VS again too.

GO is a batch separator.

DDL changes aren't necessarily visible in the same batch as they were created. Without GO, you're creating the procedure, then trying to call it, but it's early-binding at the start of the batch and can't resolve it. The effect of GO is basically making multiple separate roundtrips to the database.

I can't say I've ever seen someone create a procedure, call it once, and drop it. That seems like a Bad Idea.

---

Filed under: It's a stored procedure, not a one-off procedure

[error]

I'm not sure if it would work, but if so it would be cleaner than a temp table:

Can you pass a table-valued output parameter? I know you'd have to declare the table type separately first.

[error]

Instead of:

if object_id('tempdb..#AllInvolvement') is null
  begin
    create table #AllInvolvement ( ... )
    insert into #AllInvolvement execute spDashboardInvestigationParticipantsGet;
end

try

declare @AllInvolvement table ( ... )
insert into @AllInvolvement execute spDashboardInvestigationParticipantsGet;

Table variables should be preferred over temp tables where possible.

Edit: Though I guess you're trying to cache values maybe?

[Captain]

@error OK I can try that. caching isn't too important, but I do need indexes on some of the other temp tables.

[Captain]

@error Yeah, but GO batches are somehow different from just a ;. So SSMS forces me to pollute my working SQL with its BS keywords. We deserve a ; that works.

[error]

@Captain said in Quick question about T-SQL and parameters:

@error Yeah, but GO batches are somehow different from ; batches and don't mix together. So SSMS forces me to pollute my working SQL with its BS keywords.

; is just a statement separator.

[Captain]

@error https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/batches-of-sql-statements?view=sql-server-ver15

Explicit Batches An explicit batch is two or more SQL statements separated by semicolons (;). For example, the following batch of SQL statements opens a new sales order. This requires inserting rows into both the Orders and Lines tables. Note that there is no semicolon after the last statement.

But then again, that's the stupid ODBC driver or whatever.

[error]

@Captain said in Quick question about T-SQL and parameters:

@error https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/batches-of-sql-statements?view=sql-server-ver15

Explicit Batches An explicit batch is two or more SQL statements separated by semicolons (;). For example, the following batch of SQL statements opens a new sales order. This requires inserting rows into both the Orders and Lines tables. Note that there is no semicolon after the last statement.

OK, I guess my terminology was off.

"GO" has the effect of you making two completely independent database calls.

[Captain]

@error Nah, you're not wrong. MS calls two different but very closely related things batches. No wonder it's confusing.

[Vixen]

@Captain said in Quick question about T-SQL and parameters:

@error Nah, you're not wrong. MS calls two different but very closely related things batches. No wonder it's confusing.

just two different things Batches?

uhh..... i find that hard to believe......

two things in SQLServer maybe, but across the whole company?! no chance!

[Captain]

Changed my test runner to split the SQL query on "GO", execute them all, and return results from the last query.

What a pain in the ass.

[Captain]

OK, did the change. Now my test runner splits the query up into batches, and runs each batch.

So, awesomely enough, I got the whole table when I passed a parameter into my test.

[izzion]

@Captain
And you were expecting that? Or a filtered subset?

When you create the Stored Procedure, are you creating it as a temp procedure, or a permanent object?

[Captain]

Sorry, I was unclear again.

I passed in the parameter so I expected filtering.

The SP is permanent, but I do create or alter. Maybe it's not detecting the parameter change? I can change it to a temporary SP easily enough.

[izzion]

@Captain
Well, I would expect the permanent SP to work - if the original batch created it as temporary and there was a permanent of the same name (but different functionality), I could see how the test harness might have called the wrong one when splitting into multiple batches.

And to confirm we're on the same page: "passed it a parameter in the test" means the test executed:
INSERT INTO #AllInvolvement EXECUTE spDashboardInvestigationParticipantsGet 1 (or @CpsId = 1 after the sproc name instead of just the value)