Calling Procs from PowerShell with Parameters

  • Jeff Moden wrote:

    frederico_fonseca wrote:

    Jeff Moden wrote:

     Other that creating a parameter table the job reads from, I don't know anyone else that has been able to do it either.

    out of curiosity what did you mean with this?

    I'm just talking about a table in a database that you could have something populate with whatever "parameters" a job needs (even in job steps to "pass parameters" to the next step).  I've also not seen people pass parameters directly from one step in a job to another.  With the understanding that I'm definitely NOT an SSIS Ninja by any stretch of the imagination, I wouldn't mind learning how to do such a thing if it can be done without such a "parameter table".

    Do you know of a way to pass parameters forward from one job step to another?

    Without a table (or a queue) there is no way to pass "data" from 1 step to another so nothing new there - SQL Agent is quite old tech as you know.

    as for using tables yes I have those - multiple step jobs that use a parameter table that both controls the flow of the steps and is updated by each step to determine what the next step(s) is/are going to do.


  • Why not just write it as a C# library DLL, and call it from PowerShell?

    Let PowerShell manage flow, let C# manage business logic.  Clean separation of concerns.

  • Given that SQL Server is expensive and licensed by the CPU core, why would you waste CPU on running the SQL Server Agent?

    I prefer to have a second computer that has a number of Windows Scheduled Tasks. A little PowerShell wrapper, or a .NET console application, can then issue the calls to SQL Server, in a manner similar to Frank's article.

  • Given that you have dbatools installed, there is an easier way to include parameters:

    Invoke-DbaQuery -SqlInstance localhost -Database AdventureWorks2017 -Query "[dbo].[uspGetBillOfMaterials]"   
    -CommandType StoredProcedure -SqlParameter @{StartProductID = $productId, CheckDate = $checkDate}
  • I fully agree with DaveRogers as his suggestion works fine in PS 7 as well as PS standard which is not the case when using the suggested solution as it does not work in PS 7

  • Is there a reason NOT to use the more concise $cmd.Parameters.AddWithValue('@StartProductID',749) instead of

    $p1 = $cmd.Parameters.Add('@StartProductID',[int])


    $p1.Value = 749

  • tom_price wrote:

    Is there a reason NOT to use the more concise $cmd.Parameters.AddWithValue('@StartProductID',749) instead of

    $p1 = $cmd.Parameters.Add('@StartProductID',[int]) $p1.ParameterDirection.Input $p1.Value = 749

    Using .AddWithValue will end up costing you a lot more than it saves in concise code.  There are quite a few issues - the most problematic is that it infers the data type from the value.  This leads to sending nvarchar strings of different lengths where varchar should be used - which then causes full table-scans instead of index seeks due to implicit conversions.

    Here are 3 articles you should review:


    There are plenty more articles about the issues with using that code.

    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

Viewing 7 posts - 16 through 21 (of 21 total)

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