Generating ms sql at run time - 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)

Generating ms sql at run time

Author

Topic

vinay789
Starting Member

40 Posts

Posted - 2009-04-20 : 08:58:48

Hi
i am a newbie i am generating birt reports i am using ms sql db i want to generate a report at run time of that user wen he logged into his account so i have uded the code like this but its not working try to solve this i am sending the code
"select distinct spaceid from owner.dbo.siteownerspaces where siteid="+siteid
"select country,state,city,fromdate,todate,url,cost from owner.dbo.myads"+spaceid"
siteids are only one but spaceids are more for that user.so i have used distinct spaceids.
Thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:59:48

Please post exact used code so that we can assist your.
Posting pseudocode doesn't help.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-20 : 09:06:01

What do you mean by "its not working"?

Madhivanan

Failing to plan is Planning to fail

vinay789
Starting Member

40 Posts

Posted - 2009-04-20 : 09:20:08

i dont know how to mix this code please mix this code so that it can generate a sql query at run time
"select distinct spaceid from owner.dbo.siteownerspaces where siteid="+siteid
"select country,state,city,fromdate,todate,url,cost from owner.dbo.myads"+spaceid"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 09:25:54

DECLARE @s VARCHAR(1000), @r varchar(1000)

Set @s = 'select distinct spaceid from owner.dbo.siteownerspaces where siteid = ' + quotename(@siteid, '''')
set @r = 'select country,state,city,fromdate,todate,url,cost from owner.dbo.' + quotename('myads' + @spaceid)

exec(@s)
exec(@r)

E 12°55'05.63"
N 56°04'39.26"

vinay789
Starting Member

40 Posts

Posted - 2009-04-20 : 09:54:10

Hi Thanx for ur response
i tried ur code its giving error saying

Error:Must declare the scalar variable "@siteid"
Must declare the scalar variable "@spaceid"
any suggestions

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-20 : 09:56:11

quote:
Originally posted by vinay789

Hi Thanx for ur response
i tried ur code its giving error saying

Error:Must declare the scalar variable "@siteid"
Must declare the scalar variable "@spaceid"
any suggestions

Declare the variables.

_________________
An infinite universe is the ultimate cartesian product.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-20 : 10:07:23

You'll probably want to assign them some values as well

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vinay789
Starting Member

40 Posts

Posted - 2009-04-20 : 10:12:08

Hi
i how to declare the variables pls rewrite the code

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 10:34:37

The topic poster is clearly trying to run sql code from the application layer (hence the +sideid concatenation to the sql string).

vinay789
Starting Member

40 Posts

Posted - 2009-04-20 : 10:34:59

how to assign the values can u explain clearly with sample code

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 11:21:43

Are you writing and running the SQL code from the application layer? (are you using asp.net, php, or java???)

OR

Are you running the SQL Code using the SQL editor?

vinay789
Starting Member

40 Posts

Posted - 2009-04-21 : 05:06:26

yes i am running SQL code using SQL editor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 05:22:15

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

Set @s = 'select distinct spaceid from owner.dbo.siteownerspaces where siteid = ' + quotename(@siteid, '''')
set @r = 'select country,state,city,fromdate,todate,url,cost from owner.dbo.' + quotename('myads' + cast(@spaceid as varchar(11)))

exec(@s)
exec(@r)

E 12°55'05.63"
N 56°04'39.26"

vinay789
Starting Member

40 Posts

Posted - 2009-04-21 : 06:10:08

Thanku peso for ur immediate response sql editor is giving exception saying that :
No Resultset was produced.
Thanx.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 06:20:24

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

set @siteid = 11
set @spaceid = 2

Set @s = 'select distinct spaceid from owner.dbo.siteownerspaces where siteid = ' + quotename(@siteid, '''')
set @r = 'select country,state,city,fromdate,todate,url,cost from owner.dbo.' + quotename('myads' + cast(@spaceid as varchar(11)))

exec(@s)
exec(@r)

E 12°55'05.63"
N 56°04'39.26"

vinay789
Starting Member

40 Posts

Posted - 2009-04-21 : 06:38:51

Thanx peso it is generating spaceids of related siteid but i want to get this data at run time without declaration and also i want to display the whole fields i.e., country,state,city,fromdate,todate,url,cost fields also
any suggestions please

vinay789
Starting Member

40 Posts

Posted - 2009-04-21 : 09:41:58

Hi i am passing string at run time and getting the spaceid's for that user but below code is giving
user as output pls verify this code and send me

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

set @user ='cat'
set @spaceid =''

Set @s = 'select user from staging.dbo.site where user = ' + quotename(@spaceid,'''')
set @r = 'select country,state,city,fromdate,todate,url,cost from staging.dbo.' + quotename('myads' + cast(@spaceid as varchar(11)))

exec(@s)
exec(@r)

Thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources