Use parameters with Execute SQL task

Question

Use parameters with Execute SQL task

Kathi Kellenberger

SSChampion

Points: 11811
More actions
August 6, 2005 at 9:42 am

#165548

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
August 9, 2005 at 8:00 am

This was removed by the editor as SPAM

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

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

Rootman SSCrazy Points: 2641 More actions · Answer 1

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.

JB-242273 SSC Enthusiast Points: 165 More actions · Answer 2

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

. . .

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

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

Jamie Thomson SSChampion Points: 11805 More actions · Answer 4

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

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

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

David Vann SSC Journeyman Points: 76 More actions · Answer 6

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 7

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 8

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

David Vann SSC Journeyman Points: 76 More actions · Answer 9

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.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 10

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

Kirk Haselden SSCommitted Points: 1683 More actions · Answer 11

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 12

Thanks, Kirk.

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

Jamie Thomson SSChampion Points: 11805 More actions · Answer 13

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

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson