September 22, 2009 at 5:32 pm
How can I use named parameters passing for stored procs in OLEDB Command transformation ?
For example, say I have stored proc
create proc LookupValue
@Key1 varchar(20) = NULL,
@Key2 varchar(20) = NULL,
@Value int OUTPUT
AS
BEGIN
---- Some logic to lookup value using supplied non null keys
END
I want to invoke this proc using named parameters as follows
EXEC LookupValue @Key2 = 'x'
or
EXEC LookupValue @Key1 = 'y'
or
EXEC LookupValue @Key2 = 'x', @Key1 = 'y'
Can I set OLE-DB Command transformation's sql command property to
EXEC LookupValue @Key1 = ?,@Key2 = ?
and pass parameters to it.
Thanks
September 22, 2009 at 9:01 pm
It is possible, sort of, but the problem is that you have to set the command and you can't change it. What that means is that you can't change what named parameters are used from call to call.. I'm not sure that is what you really wanted..
CEWII
September 22, 2009 at 9:10 pm
I wouldn't be changing the Command. Can I get the syntax for using the named parameter in the Oledb command?
September 22, 2009 at 9:14 pm
EXEC dbo.Test1 @Key1 = ?, @Key2 = ?
CEWII
September 22, 2009 at 9:32 pm
Hi-
EXEC dbo.Test1 @Key1 = ?, @Key2 = ?
This will work if I use both parameters, In my case I only want to send values for second parameter alone.
On this case, If give
EXEC dbo.Test1 @Key2 = ?
Then it is taking the @Key2 as the first parameter to the stored procedure. Any ideas?
September 24, 2009 at 6:26 am
I use procs all the time with OLEDB commands and I
always pass values to all parms. I don't think there's a
way out of it. The only (chintzy) thing you coudl do is
have a diff version of the proc with only the parm you need
to pass a value to. Or, leave the un-needed parm out altogether?
September 24, 2009 at 9:36 am
In my case, I cannot change the parameters of a stored procedure because those stored procs are created by the ERP system and code locked.
However, I would take the alternate you suggested with a little change.
Create a new stored proc with the parameters (Which I only need to pass) and then with in the new stored proc make the call to the original stored proc with those parameters.
I hope this will work and Thanks for your suggestion.
September 24, 2009 at 10:53 am
G2Know (9/22/2009)
Hi-EXEC dbo.Test1 @Key1 = ?, @Key2 = ?
This will work if I use both parameters, In my case I only want to send values for second parameter alone.
On this case, If give
EXEC dbo.Test1 @Key2 = ?
Then it is taking the @Key2 as the first parameter to the stored procedure. Any ideas?
Then @Key1 is passed a NULL, I think you might have missed an important phrase above.. The query (or sproc in this case) cannot change during runtime, or from row to row, your sproc has to be smart enough to handle it. The only way I could think to do it might involve a conditional split, but then you have a bunch of different OLEDB commands all doing a part of the work. Either way, the commands in each OLEDB command would be SET and would not change during runtime..
CEWII
September 24, 2009 at 10:54 am
tiffanyjanetblack (9/24/2009)
I use procs all the time with OLEDB commands and Ialways pass values to all parms. I don't think there's a
way out of it. The only (chintzy) thing you coudl do is
have a diff version of the proc with only the parm you need
to pass a value to. Or, leave the un-needed parm out altogether?
I would look at the conditional split idea before I added skeletal sprocs to the DB to get around this..
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply