FullyQualified name with parameters from SP

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

FullyQualified name with parameters from SP

Author

Topic

learntsql

524 Posts

Posted - 2009-10-23 : 01:14:25

Hi guys,
Can i cal my sql statements somthing like this in my stored procedure.
create proc DynamicDBSchema
(@dbName varchar(50),@SchemaName varchar(50))
as
begin
select * from @dbName.@SchemaName.<TableName>
end

Kabila
Starting Member

33 Posts

Posted - 2009-10-23 : 01:29:11

create proc DynamicDBSchema
(@dbName varchar(50),@SchemaName varchar(50))
as
begin
declare @str varchar(100)
set @str='select * from '+ @dbName +'.'+@SchemaName +'.'+ '<TableName>'
exec(@str)
end

learntsql

524 Posts

Posted - 2009-10-23 : 02:03:43

This os Ok
but i have hundreds of sps and function created in my dbs.
i cant change all sps and functions into dynamic sql i think its quite complex.
Is there any alternative.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-23 : 02:14:31

quote:
Originally posted by learntsql

This os Ok
but i have hundreds of sps and function created in my dbs.
i cant change all sps and functions into dynamic sql i think its quite complex.
Is there any alternative.

As of I know Dynamic SQL is the way to solve your problem!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

learntsql

524 Posts

Posted - 2009-10-23 : 03:36:51

Thanks for ur reply.
using dynamic sql in all sps is not preferable i think
any alternative plz.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-23 : 03:40:15

Why do you need to do this ?

What is the purpose of this ?

Why can't you fixed the schema / table name in the stored procedure ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources