Dynamic SQL - 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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL

Author

Topic

Access
Starting Member

44 Posts

Posted - 2007-08-07 : 23:10:03

I need a help with the following scenario.

I have two dynamic SQL like this:
DECLARE @sSQL_ID varchar(1000)
SELECT @sSQL_ID= 'SELECT ID FROM Store WHERE StoreLocation='New York'

DECLARE sSQL varchar(1000)
SELECT @sSQL = 'SELECT * FROM City WHERE StoreID = @sSQL_ID

Of course the second statement (the one in bold) is incorrect.
What I need to do is to pass a value returned from first dynamic sql(@sSQL_ID) into second(@sSQL), so it will read something like this SELECT @sSQL = 'SELECT * FROM City WHERE StoreID = 10

Thank you

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-07 : 23:29:47

Try this:

SET @sSQL = 'SELECT * FROM City WHERE StoreID = ' + @sSQL

But you may not get correct result if first statement returns multiple values.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 23:39:10

why don't you use INNER JOIN ?

SELECT *
FROM   Store s INNER JOIN City c
       ON s.ID = c.StoreID
WHERE  s.StoreLocation = 'New York'

KH
[spoiler]Time is always against us[/spoiler]

Access
Starting Member

44 Posts

Posted - 2007-08-07 : 23:40:04

quote:
Originally posted by rmiao

Try this:

SET @sSQL = 'SELECT * FROM City WHERE StoreID = ' + @sSQL

But you may not get correct result if first statement returns multiple values.

Hello,
It will not work because @sSQL_ID holds basically a string(SELECT ID FROM Store WHERE StoreLocation='New York') and not the actual ID value.

Thanks

Access
Starting Member

44 Posts

Posted - 2007-08-07 : 23:41:19

quote:
Originally posted by khtan

why don't you use INNER JOIN ?

SELECT *
FROM Store s INNER JOIN City c
ON s.ID = c.StoreID
WHERE s.StoreLocation = 'New York'

I just gave a small part of the query. I have to use a dynamic SQL here.

Thanks

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 23:44:04

quote:
I just gave a small part of the query. I have to use a dynamic SQL here.

So you can't use INNER JOIN at all in your BIG part of the query ? And why Dynamic SQL ?

KH
[spoiler]Time is always against us[/spoiler]

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-07 : 23:47:27

Your first query should be:

SELECT @sSQL_ID = ID FROM Store WHERE StoreLocation = 'New York'

Access
Starting Member

44 Posts

Posted - 2007-08-07 : 23:50:18

quote:
Originally posted by khtan

quote:
I just gave a small part of the query. I have to use a dynamic SQL here.

So you can't use INNER JOIN at all in your BIG part of the query ? And why Dynamic SQL ?

No , this is the case where i can not use INNER JOIN .
It is based on the passing parameters, some can be empty.
The COALESCE didn't work in my case, so i had to do a dynamic SQL, i'm also trying to do a DB paging in there as well.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-08 : 00:09:33

quote:
It is based on the passing parameters, some can be empty.
The COALESCE didn't work in my case

Post sample of such scenario ?

KH
[spoiler]Time is always against us[/spoiler]

Access
Starting Member

44 Posts

Posted - 2007-08-08 : 00:27:25

quote:
Originally posted by khtan

quote:
It is based on the passing parameters, some can be empty.
The COALESCE didn't work in my case

Post sample of such scenario ?

I really think i should not start explaining it in here, because after all this post will go in totally different direction.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-08 : 00:52:16

So what is the status your problem now ?

KH
[spoiler]Time is always against us[/spoiler]

Access
Starting Member

44 Posts

Posted - 2007-08-08 : 02:00:36

quote:
Originally posted by khtan

So what is the status your problem now ?

KH
[spoiler]Time is always against us[/spoiler]

I've got an answer from another forum:

declare @loc varchar(100),
@sql1 nvarchar(1000)

set @loc = 'new york'
set @sql1 = 'select * from city where id=(select id from store where storelocation=@loc)'

exec sp_executesql @sql1, n'@loc varchar(100)', @loc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-08 : 02:26:26

quote:
Originally posted by Access

quote:
Originally posted by khtan

So what is the status your problem now ?

KH
[spoiler]Time is always against us[/spoiler]

I've got an answer from another forum:

declare @loc varchar(100),
@sql1 nvarchar(1000)

set @loc = 'new york'
set @sql1 = 'select * from city where id=(select id from store where storelocation=@loc)'

exec sp_executesql @sql1, n'@loc varchar(100)', @loc

If you can accept this, why not use INNER JOIN syntax as i posted earlier ? in most cases using INNER JOIN is faster than IN.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources