Use parameters with Execute SQL task

  • I would like to use a parameter with the execute sql task but nothing is working like BOL says it should. I am using the June CTP.

    I have a query something like

    Select ProductID, ProductName

    from Production.Product

    Where ProductID = ?

    The Parameter mapping page looks like this:

    User:roductID, Input, Long, ?

    Clicking Parse Query returns this: "The query failed to parse. Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

    BOL also says you can do this

    Select ProductID, ProductName

    from Production.Product

    where ProductID = @Param1

    Parameter Mapping page:

    User:roductID, Input, Long, @Param1

    OR

    User:roductID, Input, Long, ?

    Returns error "The query failed to parse. Must declare the scalar variable @Parm1.".

    The only way to clear this error is by declaring @Param1 inside the SQL statement.

    Declare @Param1 int

    Select ProductID, ProductName

    from Production.Product

    where ProductID = @Param1

    But this doesn't actually send User:roductID in place of @Param1.

    I have also tried this

    Select ProductID, ProductName

    from Production.Product

    where ProductID = Param1

    with Parameter Mapping page:
    User:roductID, Input, Long, Param1

    The query will parse, but errors out when executed. By running Profiler, I can see that the statement is passed to SQL as is, without the replacement.

    Any help will be appreciated. I have a big deadline in a couple of days.

    Kathi

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • This was removed by the editor as SPAM

  • Hello Kath

    I experienced similar problems when trying to do this.  In the end I resorted to a stored proc, and passing the parameters seemed to work just fine.

     

     

     

  • what data type of your variable???

    i experiance the same problem in the past i set to my variable type Int, however i changed the type to integer or decimal and it's work.

     

     

     

    ------------------------------------------------------------

    Imagination is more important then knowledge

     

    . . .

  • I finally got a good answer on this one.  Use '?' as the placeholder in the query and use numbers starting at 0 for the parameter name.  Works like a charm!

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Kathi,

    I think BOL is a bit light on this. Could you use the "Send Feedback" link on the appropriate page to let the author know - it seems alot of people have had this problem!

     

    -Jamie

     

  • I talked with a developer at MS about this. He said that the parameters for each provider is different and right now, there is no source listing the differences. There should be a white paper by RTM.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I don't understand how this worked.   Can you send a sample?

  • This parameter stuff just about drove me nuts. All my 2005 stuff is at home, so I'll try to post how I solved the problem tonight.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Depending on the type of connection, the parameters are set up differently. Even then, things didn't always work for me. If you use an ADO.NET connection manager, the parameters should start with the @ symbol.  If you use the OLE DB connection manager, the parameters are numeric starting with 0. I had the best luck with the ADO.NET connection manager.

    I am trying to find out from MS if there is a white paper available. I'll post what I find out here.

     

     

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I didn't mean for you to put some much effort into this.  Thank you for all your help.   I can make it work another way if I have to.  I was just trying to make it cleaner.

  • No problem. I just had to go back and find some information I had previously researched.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Thanks, Kirk.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • A method which always works regardless of the provider is to build the whole SQL statement dynamically using expressions.

    I always use that approach and so far...so good.

    -Jamie

     

Viewing 15 posts - 1 through 15 (of 20 total)

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