SEND THE DATABASE NAME AS A PARAMETER

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

SQL Server Development (2000)

SEND THE DATABASE NAME AS A PARAMETER

Author

Topic

pipi
Starting Member

18 Posts

Posted - 2005-06-07 : 02:13:08

GOODMORNING

I WANT TO PASS THE DATABASE NAME AS A PARAMETER IN A STORED PROCEDURE.
e.g
CREATE PROCEDURE [dbo].[GetNacisEpit]
@pela1 varchar(6),
@pela2 varchar(6),
@xrhsh varchar(20)
AS

SELECT koe2.Koe_typo_kata as 'TypoKata',
'EjoFlhmenes'=(select COUNT(koe1.Koe_ariu_epit)
from @xrhsh.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 1 and koe1.Koe_loga_kata=koe2.Koe_loga_kata )),
'AjiaEjoflhmenes'=(select isnull(sum(koe1.Koe_poso_epit) ,0.00)
from @xrhsh.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 1 and koe1.Koe_loga_kata=koe2.Koe_loga_kata ))
FROM @xrhsh.[DBO].KOE as koe2
where koe2.koe_loga_pela between @pela1 and @pela2
GROUP BY koe2.Koe_typo_kata

when i execute this doesn't work

exec GetNacisEpit '00001','00001','csbxalkida'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-07 : 02:26:23

You need to use Exec
eg

Declare @dbname varchar(20)
set @dbName='Master'
exec('Select * from '+@dbname+'.dbo.sysobjects')

Madhivanan

Failing to plan is Planning to fail

pipi
Starting Member

18 Posts

Posted - 2005-06-07 : 02:35:29

sorry,but why
exec('Select * from '+@dbname+'.dbo.sysobjects')

it seems that 'from @dbname.[dbo].table ' doesn't work

i try also this

declare @dbase varchar(20)
set @dbase ='csbxalkida'

but nothing

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-07 : 02:42:03

'Select * from '+@dbname+'.dbo.sysobjects' -- @dbname will be replaced by what @dbname has
Select * from @dbname.dbo.sysobjects --will not work as @dbname itself treated as Dbname and not what it has

declare @dbase varchar(20)
set @dbase ='csbxalkida'
exec('Select * from '+@dbname+'.dbo.sysobjects')

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-07 : 03:01:56

quote:
Originally posted by pipi

sorry,but why
exec('Select * from '+@dbname+'.dbo.sysobjects')

it seems that 'from @dbname.[dbo].table ' doesn't work

What you are doing is building a string that contains a valid SQL statement, then executing it.

Have a read of these :
http://www.sqlteam.com/item.asp?ItemID=4599
and
http://www.sqlteam.com/item.asp?ItemID=4619

they should explain what you need to do.

Damian
Ita erat quando hic adveni.

pipi
Starting Member

18 Posts

Posted - 2005-06-07 : 03:18:59

this execution doesn't work

select *->returns all rows of csbxalkida.dbo.sysobjects,

i want stored procedure to run for the tables in other database(@dbase)
e.g
declare @dbase varchar(20)
set @dbase ='csbxalkida'
select *
from '+@dbase+'.dbo.tablename

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-07 : 03:28:53

quote:

this execution doesn't work

select *->returns all rows of csbxalkida.dbo.sysobjects,

That is correct, it was given to you as example code.
Now, go read the articles so you can UNDERSTAND why.

Damian
Ita erat quando hic adveni.

pipi
Starting Member

18 Posts

Posted - 2005-06-07 : 04:22:35

thank u very much!!!
i tried to execute my guery,but i have a problem,cause my query uses subqueries.
e.g

declare @dbase varchar(20)
set @dbase ='csbxalkida'

exec('SELECT koe2.Koe_typo_kata as 'TypoKata',
koe2.Koe_loga_kata 'KataLoga',
koe2.Koe_loga_pela 'KodiPela',
'StonAera'=(select COUNT(koe1.Koe_ariu_epit)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 0 and koe1.Koe_loga_kata=koe2.Koe_loga_kata )),
'AjiaStonAera'=(select isnull(sum(koe1.Koe_poso_epit) ,0.00)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 0 and koe1.Koe_loga_kata=koe2.Koe_loga_kata )),
'EjoFlhmenes'=(select COUNT(koe1.Koe_ariu_epit)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 1 and koe1.Koe_loga_kata=koe2.Koe_loga_kata )),
'AjiaEjoflhmenes'=(select isnull(sum(koe1.Koe_poso_epit) ,0.00)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 1 and koe1.Koe_loga_kata=koe2.Koe_loga_kata ))
FROM '+@dbase+'.[DBO].KOE as koe2
where koe2.koe_loga_pela between @pela1 and @pela2
GROUP BY koe2.Koe_typo_kata, koe2.Koe_loga_kata, koe2.Koe_loga_pela
order by koe2.Koe_typo_kata, koe2.Koe_loga_pela,koe2.Koe_loga_kata')

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-07 : 04:28:28

Remove the quotes from your column aliases as you dont need to use them

Example
SELECT OrderID AS OrderNumber
FROM Orders

SELECT OrderID AS 'OrderNumber'
FROM Orders

Both queries work

Beauty is in the eyes of the beerholder

pipi
Starting Member

18 Posts

Posted - 2005-06-07 : 05:04:47

ok,thanks ii works,but i have another problem
i want to return the rows of the select in a temporary table.

if i use exec('select..........')
does not find the #table

e.g
declare @dbase varchar(20)

declare @pela2 varchar(6)
declare @pela1 varchar(6)
set @dbase ='csbxalkida'

exec('SELECT koe2.Koe_typo_kata as TypoKata,
koe2.Koe_loga_kata KataLoga,
koe2.Koe_loga_pela KodiPela,
StonAera=(select COUNT(koe1.Koe_ariu_epit)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 0 and koe1.Koe_loga_kata=koe2.Koe_loga_kata )),
AjiaStonAera=(select isnull(sum(koe1.Koe_poso_epit) ,0.00)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 0 and koe1.Koe_loga_kata=koe2.Koe_loga_kata )),
EjoFlhmenes=(select COUNT(koe1.Koe_ariu_epit)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 1 and koe1.Koe_loga_kata=koe2.Koe_loga_kata )),
AjiaEjoflhmenes=(select isnull(sum(koe1.Koe_poso_epit) ,0.00)
from '+@dbase+'.[DBO].koe as koe1
where (koe1.koe_kash_epi1 like 0 and koe1.koe_kash_epi2 like 0 and koe1.koe_kash_epi3 like 1 and koe1.Koe_loga_kata=koe2.Koe_loga_kata ))
into #MISTEMP
FROM '+@dbase+'.[DBO].KOE as koe2
--where koe2.koe_loga_pela between @pela1 and @pela2
GROUP BY koe2.Koe_typo_kata, koe2.Koe_loga_kata, koe2.Koe_loga_pela
order by koe2.Koe_typo_kata, koe2.Koe_loga_pela,koe2.Koe_loga_kata')

select TypoKata,
KataLoga,
KodiPela,
StonAera,
AjiaStonAera,
EjoFlhmenes,
AjiaEjoFlhmenes
from #MISTEMP
where TypoKata<>'XORIGHSHS'
drop table #MISTEMP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-07 : 05:12:52

Dont use the prefix # use table name as MISTEMP

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-07 : 05:18:42

quote:

i want to return the rows of the select in a temporary table.

Create the temp table first.

Create Table #MyTable (
mycolumn int,
myothercolumn varchar(10)
)

-- now run your dynamic sql that inserts into #MyTable
Declare @sql varchar(5000)
SET @SQL = 'INSERT INTO #MyTable SELECT blah blah'
Exec (@Sql)

quote:

Dont use the prefix # use table name as MISTEMP

This is a really bad idea Madhivanan, if more than one user tries to run this query you're going to end up with a mess.

Damian
Ita erat quando hic adveni.

pipi
Starting Member

18 Posts

Posted - 2005-06-07 : 05:21:46

thank u!!i tried without temporary and it works.
sorry for all these questions ,but i have another problem

i want to select a field with case clause in previous select
e.g

case TypoKata
when 'OPSEOS' then (select isnull(sum(kok_poso_xreo)-sum(kok_poso_pist),0.00) from '+@dbase+'.[DBO].kok where kok_loga_opse=KataLoga)
when 'TAMIEYTIRI' then (select isnull(sum(ktk_poso_xreo)-sum(ktk_poso_pist),0.00) from '+@dbase+'.[DBO].ktk where ktk_loga_tami=KataLoga)
when 'PROUESMIAS' then (select isnull(sum(kpk_poso_xreo)-sum(kpk_poso_pist),0.00) from '+@dbase+'.[DBO].kpk where kpk_loga_prou=KataLoga)
end as Ypoloipo

when i execute the query:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'OPSEOS'.

pipi
Starting Member

18 Posts

Posted - 2005-06-07 : 05:36:08

YOU'RE RIGHT MERKIN ABOUT THE TEMPORARY TABLE
I CREATED FIRST AND IS OK.

MY PROBLEM IS THAT WHEN IN THS SQL STRING I USE ANOTHER STRING,E.G
SELECT CASE COLUMN
WHEN 'A' THEN .....
WHEN 'B' ....

ERROR:Incorrect syntax near 'A'.

HAVE YOU ANY IDEA?

P.S SORRY FOR MY BAD ENGLISH.I'M FROM GREECE AND MY ENGLISH IS NO GOOD

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-07 : 05:47:33

Instead of one quote, try using two quote marks next to each other thusly ''A''

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources