Passing Database Name as Parameter to a SQL Script File

• Geeth Ramesh

  SSC Veteran

  Points: 280

  More actions

  June 7, 2005 at 11:02 pm

  #67100

  Hi,

  Is it possible to pass a Database name as a Parameter to an SQL Script File.

  Find below an example of the Script File(.sql):

  USE ANVAT

  GO

  IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[GetCustomerID]')

  and type = 'FN')

  DROP FUNCTION dbo.GetCustomerID

  GO

  CREATE FUNCTION GetCustomerID (@strVATNum varchar(30))

  RETURNS varchar(30)

  AS

  BEGIN  

     RETURN(SELECT TOP 1 BRUTC.dbo.RM00101.CUSTNMBR FROM BRUTC.dbo.RM00101

     WHERE TXRGNNUM like 'BE%'

     AND ANVAT.dbo.ConvertVatNum(TXRGNNUM) = ANVAT.dbo.ConvertVatNum(@strVATNum))

  END

  GO

   

  In the above mentioned script file, the hard coded Database name "ANVAT", needs to be replaced by a parameter, so that the user can choose to create these scripts in any database.

  Can anyone please help me in this regard?

  Thanks,

  Geeth Ramesh.

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  June 8, 2005 at 8:15 am

  #563825

  It would need to build a dyanic query which will lead to security lapses/issues.

  (Normally db name is determined by the connection unless there is a need to make external call. When you made use ANVAT Then there is no need to prefix the same database name.)

  Is it possible to use db_name() some thing like

  if db_name() = 'ANVAT'

  begin

  end

  Regards,
  gova

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  June 8, 2005 at 9:00 am

  #563844

  search this site for info on the topic!

  Here're a couple of links discussing this...

  http://www.sqlservercentral.com/forums/userinfo.aspx?id=132447

  http://www.sqlservercentral.com/forums/userinfo.aspx?id=153059

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**