How to Pass Object Type Variable as Parameter for IN clause in query

Question

How to Pass Object Type Variable as Parameter for IN clause in query

HakunaMatata

SSCrazy

Points: 2610
More actions
September 13, 2010 at 9:40 am

#145173

I have to write a query which performs select operation on one table from one database on server A and where condition has result of another query which is fired against different server.
Now the question is that, I can't setup linked servers for this. I have written this query in SQL task.
What I have figured out is I can write two sql tasks, one will take out the results of inner select query that I will be using in where clause and store it in a variable. Since it retrives multiple rows, I have taken variable type as Object. Now how can I pass this object type varible to a query in another SQL task as a where parameter?
for eg. this variable with object type will contain values as -
VALUE1
VALUE2
VALUE3
and I want to pass this values in where clause in another query as IN parameter..!
Any idea on this?
:rolleyes:

Viewing 5 posts - 1 through 4 (of 4 total)

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

Martin_A SSC-Addicted Points: 432 More actions · Answer 1

Hi sacheen.

I became curious how to solve this also and after some searching the net I just got something working ok. I will try to describe it briefly below:

- After retrieving the Object variable containing the IN-clause for the outer select, add a script task in the flow and set the Object variable as a readonly variable and define another String variable as a read/write variable.

- In the script you can now convert the Object variable into a DataTable object and fill this DataTable with all the values from the Object variable, and then loop through all values. When looping through the datatable, add each value to the writable String variable I mentioned above by using string concatenation. Something like "MyINClause = MyINClause + datatable.currentvalue + ", ".

- In this way you build up the IN clause as a string, adding value for value from the Object variable. When the loop and script is finished you can use the value of the string variable as the IN-clause in your outer select query.

I realise this is probably not the best solution to your problem. For example if the inner select query returns a very large number of values, it might be too much data to be suitable to convert into a string variable.

But as a method it worked ok for a couple of test values and perhaps it is good enough for you too and better than nothing anyway 🙂

I hope it helped. Feel free to ask if you want more details.

This page helped me with the code for the script and how to convert the Object variable into a DataTable object:

http://consultingblogs.emc.com/jamiethomson/archive/2006/01/04/2540.aspx

/Martin

sacheen (9/13/2010)
I have to write a query which performs select operation on one table from one database on server A and where condition has result of another query which is fired against different server.
Now the question is that, I can't setup linked servers for this. I have written this query in SQL task.
What I have figured out is I can write two sql tasks, one will take out the results of inner select query that I will be using in where clause and store it in a variable. Since it retrives multiple rows, I have taken variable type as Object. Now how can I pass this object type varible to a query in another SQL task as a where parameter?
for eg. this variable with object type will contain values as -
VALUE1
VALUE2
VALUE3
and I want to pass this values in where clause in another query as IN parameter..!
Any idea on this?

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

sacheen (9/13/2010)
I have to write a query which performs select operation on one table from one database on server A and where condition has result of another query which is fired against different server.
Now the question is that, I can't setup linked servers for this. I have written this query in SQL task.
What I have figured out is I can write two sql tasks, one will take out the results of inner select query that I will be using in where clause and store it in a variable. Since it retrives multiple rows, I have taken variable type as Object. Now how can I pass this object type varible to a query in another SQL task as a where parameter?
for eg. this variable with object type will contain values as -
VALUE1
VALUE2
VALUE3
and I want to pass this values in where clause in another query as IN parameter..!
Any idea on this?

This could be done using a CTE

The CTE/sub query will contain result from your remote server...you may use OPENROWSET as Linked server is not an option

The CTE/sub query will then be embbeded with parent query

Raunak J

HakunaMatata SSCrazy Points: 2610 More actions · Answer 3

Hi Martin,

Your solution worked really well for me... !!!

Thanks a lot for the efforts you took to try this.. 😎

Hey Raunak,

I dropped idea of not using linked server because I dont have admin rights on that sql server and also dba dont allow me to create linked server. In case of OPENROWSET also, it needs one to have admin rights on the sql server as far as I have tried to execute it.!

Regards,

Sacheen

:rolleyes:

Martin_A SSC-Addicted Points: 432 More actions · Answer 4

Glad to be of help 🙂

sacheen (9/14/2010)
Hi Martin,
Your solution worked really well for me... !!!
Thanks a lot for the efforts you took to try this.. 😎
Hey Raunak,
I dropped idea of not using linked server because I dont have admin rights on that sql server and also dba dont allow me to create linked server. In case of OPENROWSET also, it needs one to have admin rights on the sql server as far as I have tried to execute it.!
Regards,
Sacheen