1. Home
  2. Categories
  3. Coding Help
  4. Quick question about T-SQL and parameters

Quick question about T-SQL and parameters

  • C

    @izzion derp, the sp call was to insert into #AllInvolvement execute #spDashboardInvestigationParticipantsGet;

    Trying #spDashboardInvestigationparticipantsGet @CpsId;

    But then I get the VS error... "Msg 137, Level 15, State 2, Line 441
    Must declare the scalar variable "@CpsId"."

    argh.

    1 izzion 1 Reply
  • izzion
    I survived the hour long Uno hand

    @Captain
    Either specify @CpsId = X, or declare it in the same batch.

    Optionally, you can also just specify X -- stored procedures will map parameters in the order they're declared... and if you only have 1 parameter declared, it isn't really that confusing to not name the parameter when executing.

    2
  • Tsaukpaetra
    Notification Spam Recipient

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

    split the SQL query on "^GO$"

    Keep in mind GO must be separate on its own line with nothing before (or after if memory serves).

    2
  • C

    I think I'm giving up on my goal of getting the query to work with VS and my (Dapper based test runner) and PowerBI simultaneously. There is just a mismatch between how Dapper (and also SSRS) and VS handle parameters.

    If I load a SQL file and pass in parameters with Dapper (or SSRS), the parameters get defined and passed into the query. Indeed, I'm expected to just "use" the parameters in my query without declaring them in it and rely on the outer program to define and pass them in.

    Which is basically fine when I'm "always" going to pass a parameter in -- i.e., like when all I'm doing is using Dapper and SSRS to get data for a single investigation. (Yeah, I have to manually plug ID's into the query to run it in VS, which is a little annoying but not a big deal).

    But it SUCKS for my current case, where I want to "spot check" a query that returns data for a TON of investigations, by making sure it does the right thing on some specific investigations.

    In this case, if I declare the variable but don't define it, that empty declaration overrides what the test runner passes in.

    But if I don't declare it, VS (and presumably PowerBI) will crap their pants because the parameter isn't defined anywhere.

    1 Vixen 1 Reply
  • Vixen

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

    There is just a mismatch between how Dapper (and also SSRS) and VS handle parameters.

    if only there were some standard they could all use to handle things the same way for interoperation?

    Something like a Structured Query Language.

    oh... right. T-SQL, MySQL, OravleSQL, Postgres.......

    man fucking implementors fucking up standards!

    1 C 1 Reply
  • C

    @Vixen Yeah, I mean just the fact that VS and other MS products allow "GO" sql files in some places and not others is pretty fuckin ridiculous.

    Someone at Microsoft must have thought they were so smart to put random non-standards compliant keywords in some SQL files and not others.

    0
Log in to reply