T-SQL select statement modification

Question

T-SQL select statement modification

bvinay57

SSC Veteran

Points: 224
More actions
May 12, 2009 at 6:51 am

#204600

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-2 VARCHAR(1000), @r varchar(1000)
set @username='vinay@gmail.com'
set @spaceid =''
set @s-2 = '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)

Viewing 15 posts - 1 through 15 (of 15 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

It's either because you have not specified any value for the variable @spaceid or the 2nd statement

set @r = 'select country,state,city from staging.dbo.' + quotename('myadvertises'+ cast(@spaceid as varchar(30)),'''') is incorrect.

--Ramesh

bvinay57 SSC Veteran Points: 224 More actions · Answer 2

Hi Ramesh thanx for ur response yes @r line is not working can u suggest me how to retrive the data from second table i mean the table at @r

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Change the 2nd statement (i.e. @r) to

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

-- OR

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

--Ramesh

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 4

Please give us some sample data so we can better help you:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

bvinay57 SSC Veteran Points: 224 More actions · Answer 5

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-2 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)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

Check if this code works for you?

DECLARE @username varchar(30),

@s-2 VARCHAR(MAX)

SELECT@username = 'vinay@gmail.com'

SELECT@s-2 = ISNULL( @s-2 + CHAR(10) + 'UNION ALL' + CHAR(10), '' )

+ 'SELECT''' + CONVERT( VARCHAR(30), spaceid ) + ''' AS SpaceID, country, state, city '

+ 'FROMstaging.dbo.[myadvertises'+ CONVERT( VARCHAR(30), spaceid ) + ']'

FROM(

SELECTDISTINCT spaceid

FROMstaging.dbo.siteownerspaces

WHEREusername = @username

) O

PRINT @s-2

--EXECUTE( @s-2 )

--Ramesh

bvinay57 SSC Veteran Points: 224 More actions · Answer 7

Hi Ramesh Thank u very much ur guys r so great its working great with little modification its came with execute(@s) but i didnt understand some code especially at

FROM (

SELECT DISTINCT spaceid

FROM staging.dbo.siteownerspaces

WHERE username = @username

) O

so please explain the code

Thank u very much once again

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 8

I am glad that it worked well.

Now for the explanation, this section

FROM (

SELECT DISTINCT spaceid

FROM staging.dbo.siteownerspaces

WHERE username = @username

) O

is called as "Derived Table". A derived table is just a normal table except that it is used when you want to select calculated/computed columns, filter rows, re-use the calculated columns in main query etc.

Here, the purpose of the sub-query is to get DISTINCT spaceids from the table, which is further used to build a dynamic SQL.

--Ramesh

bvinay57 SSC Veteran Points: 224 More actions · Answer 9

bvinay57

SSC Veteran

Points: 224

May 13, 2009 at 5:55 am

#993592

Thanx a lot for ur explanation.

bvinay57 SSC Veteran Points: 224 More actions · Answer 10

Hi Ramesh i have little problem in the colum of state i have some null values in the place of the null values i want to get 'false' so i have tried this ISNULL(state,'false') AS state but its not working any suggestions

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 11

Add the following in place of "state"

ISNULL( NULLIF( LTRIM( RTRIM( [state] ) ), '' ), 'false' )

--Ramesh

bvinay57 SSC Veteran Points: 224 More actions · Answer 12

Hi thanx for ur response i have added ur code

ISNULL( NULLIF( LTRIM( RTRIM( [state] ) ), '' ), 'false' )

in the place of state its giving error that

"Incorrect syntax near the keyword 'all' "

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 13

Can you post the exact query that gives you this error?

--Ramesh

bvinay57 SSC Veteran Points: 224 More actions · Answer 14

DECLARE @username varchar(30),

@s-2 VARCHAR(MAX)

ok this is the query

SELECT @username = 'kiranyadav.n@gmail.com'

SELECT @s-2 = ISNULL( @s-2 + CHAR(10) + 'UNION ALL' + CHAR(10), '' )+ 'SELECT ''' + CONVERT( VARCHAR(30), spaceid ) + ''' AS SpaceID, country,ISNULL( NULLIF( LTRIM( RTRIM( [state] ) ), '' ), 'all' ),city,fromdate,todate,cost '+ 'FROM staging.dbo.[myadvertises'+ CONVERT( VARCHAR(30), spaceid ) + ']'

FROM (

SELECT DISTINCT spaceid

FROM staging.dbo.siteownerspaces

WHERE username = @username

) O

--PRINT @s-2

EXECUTE( @s-2 )