Can I parameterise SQL for checking for the existence a table? 1

[mdav2]

I have the code below which I am going to use in the setup of databases. It works but I want to parametrise the database name in the "if exists select * ..." statement. I am very new to SQL Server and have tried to find examples of basic selects using the "EXEC" command with where clauses that have varchar criteria checks without success. I have seen some examples that suggest using the SP_EXECUTESQL command. If anyone can give me some advice on this it would be appreciated. I want to have to avoid having 3 different scripts to do the same thing if I can parameterise things.

Many thanks,

Mark.

--------------------------------------------------------------------------------
-- DECLARE VARIABLES
--------------------------------------------------------------------------------
-- The Database Name
DECLARE @DatabaseName   VARCHAR(MAX)
SET  @DatabaseName  = 'Test'

-- Command variable, used for building statements and EXEC'ing them
DECLARE @cmd VARCHAR(MAX)

-- Check for table and delete
IF EXISTS (SELECT *
           FROM [test].[dbo].[sysobjects]
           WHERE sysobjects.type = 'U'
           AND sysobjects.name='table1')
	SET @cmd= 'USE '+@DatabaseName+ ' ' + 'DROP TABLE [dbo].[table1]'
	EXEC (@cmd)

Mark Davies
Warwickshire County Council

 

[bborissov]

--------------------------------------------------------------------------------
-- DECLARE VARIABLES
--------------------------------------------------------------------------------
-- The Database Name
DECLARE @DatabaseName   VARCHAR(MAX)
SET  @DatabaseName  = 'Test'

-- Command variable, used for building statements and EXEC'ing them
DECLARE @cmd VARCHAR(MAX)

-- Check for table and delete
SET @cmd = '
IF EXISTS (SELECT *
                  FROM '+@DatabaseName+'.[dbo].[sysobjects]
                  WHERE sysobjects.type = ''U''
                    AND sysobjects.name=''table1'')
   BEGIN
	   USE '+@DatabaseName + '
       DROP TABLE [dbo].[table1]
   END'
EXEC (@cmd)

Borislav Borissov
VFP9 SP2, SQL Server

 

[IanWaterman]

Create a stored procedure

GO
/****** Object:  StoredProcedure Add name here    Script Date: 28/11/2012 13:54:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***************************************************************************************************************
* Add user deatils and SP info here
****************************************************************************************************************
*
* Procedure:	
* 
* Comments:     
*
**************************************************************************************************************** 
* Date          User							Comments
**************************************************************************************************************** 
* 
* 
****************************************************************************************************************/
CREATE PROCEDURE YOur procedurename
	@DatabaseName Varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Command variable, used for building statements and EXEC'ing them
DECLARE @cmd VARCHAR(MAX)

-
-- Check for table and delete
IF EXISTS (SELECT *
           FROM [test].[dbo].[sysobjects]
           WHERE sysobjects.type = 'U'
           AND sysobjects.name='table1')
	SET @cmd= 'USE '+@DatabaseName+ ' ' + 'DROP TABLE [dbo].[table1]'
	EXEC (@cmd) 

END

To execute SP

Execute YOurProcedurename 'Test'

Ian

 

[bborissov]

Ian,
that wouldn't work.
You test for existence of the table in "TEST" db not in the DB name you passed as parameter.

Borislav Borissov
VFP9 SP2, SQL Server

 

[IanWaterman]

OOPs

Thank you Boris, less haste more speed.

If I change this

IF EXISTS (SELECT *
FROM [test].[dbo].[sysobjects]
WHERE sysobjects.type = 'U'
AND sysobjects.name='table1')

to this

IF EXISTS (SELECT *
FROM '+@DatabaseName+'.[dbo].[sysobjects]
WHERE sysobjects.type = ''U''
AND sysobjects.name=''table1'')

Would that work or is an SP the wrong approach?

Ian

 

[mdav2]

Many thanks Borislav, i spent ages pulling my hair out on this one, the bit of hair I have. It was trying to accommodate the double single quote that caught me out.
Looks like I've learnt my one new thing for today! It also warrants a star.

Mark Davies
Warwickshire County Council