T-SQL select statement modification

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 2000 Forums

Transact-SQL (2000)

T-SQL select statement modification

Author

Topic

vinay789
Starting Member

40 Posts

Posted - 2009-05-12 : 08:47:01

Hi to everyone
I am using ms sql 2000 db i am retriving data at run time so i have used T-sql select statement.when retriving data 50% of my code is givng output i.e., it retriving spaceids of my user at run time but it is failing to get the colums like country,state,city pls help me to retive country,state,city also.
Thanx in advance this is my code

DECLARE @username varchar(30), @spaceid INT
DECLARE @s VARCHAR(1000), @r varchar(1000)

set @username='vinay@gmail.com'
set @spaceid =''

set @s = 'select distinct spaceid from staging.dbo.siteownerspaces where username = ' + quotename(@username, '''')
set @r = 'select country,state,city from staging.dbo.' + quotename('myadvertises'+ cast(@spaceid as varchar(30)),'''')

exec(@s)
exec(@r)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 08:51:53

perform a PRINT @r before executing it and see if the query is correct

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 08:54:51

i do notice that you declare @spaceid as INTEGER and you assign empty string '' to it.

the effect of set @spaceid = '' will give @spaceid the value 0

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

vinay789
Starting Member

40 Posts

Posted - 2009-05-12 : 09:33:02

Hi thanx for ur response its retriving spaceids correctly according to the username but not displaying the other colum values like country,state,city. I have checked with print @r but not getting wat to do.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 09:52:20

check the output of @r and copy and run the query is your query window and check that is is correct

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

vinay789
Starting Member

40 Posts

Posted - 2009-05-12 : 10:05:44

Hi khtan i tried ur suggestion i removed exec(@r) its still giving spaceids so the problem is with in set @r line shall u suggest me how to retive data for at @r

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 10:10:21

[code]
DECLARE @username varchar(30), @spaceid INT
DECLARE @s VARCHAR(1000), @r varchar(1000)

set @username='vinay@gmail.com'
set @spaceid =''

set @s = 'select distinct spaceid from staging.dbo.siteownerspaces where username = ' + quotename(@username, '''')
set @r = 'select country,state,city from staging.dbo.' + quotename('myadvertises'+ cast(@spaceid as varchar(30)),'''')

PRINT @r
-- exec(@s)
-- exec(@r)
[/code]
COPY the output of PRINT @r into a new query window and execute it. See what's the error and fix it. If you are not able to fix it, post the query here

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

vinay789
Starting Member

40 Posts

Posted - 2009-05-12 : 10:30:47

Hi khtan i tried ur code but its giving error saying that "no resultset was produced" any suggestions

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 10:39:18

quote:
Originally posted by vinay789

Hi khtan i tried ur code but its giving error saying that "no resultset was produced" any suggestions

Are you running this in Query Analyser ?

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

vinay789
Starting Member

40 Posts

Posted - 2009-05-12 : 10:49:08

hi khtan
yes i am running this in myeclipse database explorer(query analyzer)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 19:43:33

check that the table name is correct and there are records in there

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

vinay789
Starting Member

40 Posts

Posted - 2009-05-13 : 06:27:22

Hi everybody after searching a lot i have used the following code to retrive the logged users information the below code is working for only one record i.e., its retriving only one spaceid i have 8 spaceids in my db any suggestions how to retrive all records

DECLARE @username varchar(30), @spaceid INT
DECLARE @s VARCHAR(1000), @r varchar(1000)

set @username='vinay@gmail.com'

Select distinct @spaceid =spaceid from staging.dbo.siteownerspaces where username = @username

set @r = 'select ''' + cast(@spaceid as varchar(30)) + ''' As SpaceId, country,state,city from staging.dbo.[myadvertises'+ cast(@spaceid as varchar(30)) + ']'

exec(@r)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources