November 7, 2007 at 10:05 am
can i have a store proc with DB name as parameter as i need to run the same proc agianst diff DB's. if so what are the pros and cons?
November 7, 2007 at 1:49 pm
I am trying to do something like this, but getting syntax error
CREATE PROCEDURE [dbo].test
@DBName varchar(30)
AS
SET NOCOUNT ON
Select * FROM @DBName.dbo.emp
WHERE jobid=''
GO
November 7, 2007 at 2:06 pm
Mike Levan (11/7/2007)
I am trying to do something like this, but getting syntax errorCREATE PROCEDURE [dbo].test
@DBName varchar(30)
AS
SET NOCOUNT ON
Select * FROM @DBName.dbo.emp
WHERE jobid=''
GO
You need to use dynamic sql for this.
CREATE PROCEDURE [dbo].test
@DBName varchar(30)
AS
SET NOCOUNT ON
DECLARE @strSQL VARCHAR(1000)
SET @strSQL = 'Select * FROM '+ @DBName + '.dbo.emp
WHERE jobid='''''
EXEC(@strSQL)
--P.S. Dynamic SQL is least recommended because of performance issues and SQL Injection problems.
Prasad Bhogadi
www.inforaise.com
November 7, 2007 at 2:07 pm
November 8, 2007 at 6:42 am
I have a store proc where in it has more than 1000 lins of sql statements, how can i use dynamic sql there.
what i am trying to do is this proc is used in many other DB's but I want to save it in DB A and then use this proc in different DB's by passing DB name as parameter and run it form DB A
November 8, 2007 at 2:47 pm
have a look at this:
http://www.sqlservercentral.com/Forums/Topic417516-8-1.aspx
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply