How to run a dynamic query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to run a dynamic query

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-08-19 : 08:44:09

Hello all,
I have been using the following to execute my query
EXEC sp_executeSql @lComplete_Query, N'@output INT OUTPUT', @opRuleQueryResultTEMP OUTPUT

and the query that I recevie from my previous process is like
select Col1 from Table1

and I was just relacing the select with 'SELECT @output = ' and the query would look like
Select @output =Col1 from Table1

BUT When I received a query having Inner Query like
Select Col1 from Table1 where Col2=(select Col2 from Tabl2)
, My above mentioned logic is failed.
and the resultant query is formed like this
Select @output= Col1 from Table1 where Col2=(select @output= Col2 from Tabl2)
Can any one help me ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 09:41:56

i think what you want is this

Select @output= Col1 from Table1 where Col2 in (select Col2 from Tabl2)
are you sure this returns only a single value?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-19 : 09:49:36

Hi khufiamalik

Are you doing a simple REPLACE() string action to replace SELECT with SELECT @variable = ?

Try using RIGHT (example)


DECLARE @selectString VARCHAR(250)

SET @SelectString = 'SELECT [companyCode] FROM company WHERE [Id] IN (SELECT [Id] FROM companyType)'

SELECT @selectString

SELECT REPLACE (@selectString, 'SELECT', 'SELECT @myVar = ')

SELECT 'SELECT @myVar = ' + RIGHT(@selectString, LEN(@selectString) - 6)

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-19 : 09:51:40

Hi visakh16

I think I remember this post from a while back...

If memory serves, he's getting a string from a sp which contains his select statement and then he's wanting to execute that string and get a return value into a variable.

I think he asked us orrignially with an example without a sub-select clause and I suggested REPLACE to substitute in the variable assignment followed by a ss_executeSql call.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-19 : 09:53:48

quote:

Select @output= Col1 from Table1 where Col2 in (select Col2 from Tabl2)
are you sure this returns only a single value?

visakh16 is right.

The use of the IN keyword suggested that you are comparing to a result set in your where clause, not 1 discreet value. Are you sure that this will only return 1 value as he asks?

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources