Sql select where in using dynamic SQL

Question

0.00/5 (No votes)

See more:

Hi there,

I am using dynamic sql and I am trying to perform a 'where in' from another table, however I am having syntax issues.

What I have tried:

SQL

DECLARE @accountprofit nvarchar(max)
DECLARE @accountprofit2 nvarchar(max)
DECLARE @accountprofile nvarchar(max)
DECLARE @accountprofile2 nvarchar(max)


set @accountprofit = 'select cust_no from G.DBO.Clients'
EXEC(@accountprofit)


SET @accountprofile2 = 'SELECT ACC,
CUST
FROM ACCOUNTTABLE   AP
WHERE ap.cust in ('  + @accountprofit +  ') '

exec @accountprofile2

SET @accountprofile = 'SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER],' + '''' + @accountprofile2 + '''' + ')'

EXEC(@accountprofile)

select * from ##accountprofile

Posted 3-Sep-16 0:41am

Miss R

Updated 3-Sep-16 12:13pm

David_Wimbley

v2

Add a Solution

Comments

Suvendu Shekhar Giri 3-Sep-16 7:04am

What error it is showing?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2016-09-03T11:37:00

Without your schema (i'm to lazy to reverse engineer using var tables) this is going to be a bit difficult to be sure.

When placed into one variable, your sql looks to compile down to this

SQL

SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER],'SELECT ACC,CUST FROM ACCOUNTTABLE AP WHERE ap.cust in (select cust_no from G.DBO.Clients)')

My best guess is that your SELECT statement beside [MYSERVER] has been placed in there as a string, instead it needs to look like

SQL

SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER],(SELECT ACC,CUST FROM ACCOUNTTABLE AP WHERE ap.cust in (select cust_no from G.DBO.Clients)))

To achieve this you would do the following in your @accountprofile variable.

SQL

SET @accountprofile = 'SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER], (' + @accountprofile2 + '))'

Should this not solve your problem then if you would like to provide some schema to use to run this query against along with the actual error message i'll be happy to take a look when i have time.