May 30, 2005 at 6:50 am
Hi
We need a select statement in a stored procedure. This sp is called from a asp.net application but the source can be different databases.
@environment as varchar(8)
select va. codart, l.libel1 from @environment.dbo.vficart va
join location l
on va.codart = l.code_art
We receive an error on a '.' probably the one before dbo.
N E suggestions ?
Thx
JV
May 30, 2005 at 7:01 am
Check this out for a better way out :
May 30, 2005 at 7:49 am
Why not have the SP on all the different servers that may get called. THEN set-up your different connections inside your WEB application and call the proper connection for whichever server you need. This way you avoid DYNAMIC SQL...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 30, 2005 at 8:02 am
Here's the "one step better" I was missing
thanx aj
May 30, 2005 at 8:10 am
Hey thx for yr replies.
It seems that dynamic sql is dangerous 😉
But as we often have to create new databases, we don't want to have a list to do's when setting it up. So when it's dynamical build, the less worries we have.
That one article seems interesting but doesn't point out how to use different databases.
JV
May 30, 2005 at 8:27 am
yes it does... but it's very easy to miss.
But as you pointed out this is something to avoid if at all possible :
Get data from another database. If you for some reason have your application spread over two databases, you should not litter your code with hard-coded references with database names, because the day want you a test environment on the same server, you will have a problem. So an idea is to get the name of the other database from a configuration table, and build a dynamic SQL statement. There is however an alternative here, presuming that you can confine the operation on the other database in a procedure on its own. In such case you can do things like:
SET @sp-2 = @dbname + '..some_sp'
EXEC @ret = @sp-2 @par1, @par2...
May 30, 2005 at 9:03 am
Hey Journeyman,
Check out this stored proc:
create procedure dbo.usp_getData
(
@environment nvarchar(100)
)
as
begin
set @environment = ltrim(rtrim(@environment))
declare @errmsg as varchar(255)
if ( isnull(@environment,'') = '' )
begin
set @errmsg = 'No database provided'
raiserror 130000 @errmsg
return (1)
end
-- check whether @environment is a valid database
if ( db_id(@environment) is null )
begin
set @errmsg = 'Database ' + @environment + ' is not a valid database.'
raiserror 130000 @errmsg
return (1)
end
declare @sql as nvarchar(1000)
set @sql = N'select va. codart, l.libel1 from ' + @environment + N'..vficart va join location l on va.codart = l.code_art'
exec sp_executesql @sql
end
Regards,
JP
May 30, 2005 at 9:08 am
This would seem like a safe way to use dynamic sql.... but I would still use it to call a stored proc in the database... that way you get the advantage of a saved execution plan.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply