troubleshooting Question

MS SQL Server problems with creating stored procedure

Avatar of BhanuKandregula
BhanuKandregulaFlag for India asked on
Microsoft SQL Server 2005
3 Comments1 Solution232 ViewsLast Modified:
I am trying to create a stored procedure which will select tables from one database and create a view based on a condition and get below error, i have mentioned the stored procedure below too:

Bleow is the error message:
_____________________________
CREATE VIEW [testTb2] WITH ENCRYPTION AS
SELECT      *
FROM      test.dbo.testTb2
Msg 2714, Level 16, State 3, Procedure testTb2, Line 2
There is already an object named 'testTb2' in the database.
use test2
GO
CREATE VIEW [testTb3] WITH ENCRYPTION AS
SELECT      *
FROM      test.dbo. testTb3 where empnam IN('abc','bcd')
Msg 2714, Level 16, State 3, Procedure testTb3, Line 2
There is already an object named 'testTb3' in the database.

Stored procedure:
-----------------------------
USE [test]
GO
/****** Object:  StoredProcedure [dbo].[GenerateViews_for_each_company]    Script Date: 02/10/2012 09:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/**
*
*  Create Views of all the tables in the AdHoc Database
*  Make sure you are using the correct database
*  This assumes that:
*   - AdHoc is the SQL database name
**/
--USE test

ALTER PROCEDURE [dbo].[GenerateViews_for_each_company]

@DBNMSource sysname,
@DBNMDest sysname,
@comp1 varchar(20),
@Comp2 varchar(20)
AS
/**
*  Declare variables to hold Table names and SQL to execute when creating the views
**/
DECLARE      @tableName AS nvarchar(40)
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @clmn as nvarchar(10)
set @clmn = 'empnam'
/**
*  Create a temp table of all the adhoc tables.
**/
CREATE TABLE #Tables(TableName nvarchar(40))
INSERT INTO #Tables(TableName)
select name from sys.tables
where type= 'U'
/**

**/
DECLARE      TableCursor CURSOR FOR
      SELECT      TableName
      FROM      #Tables
OPEN      TableCursor
FETCH      NEXT FROM TableCursor
INTO      @tableName
WHILE (@@fetch_status = 0)
BEGIN
IF  EXISTS(select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name = @tableName and column_name = @clmn)
 BEGIN
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'use ' + @DBNMDest;
SET @strPrint = @strPrint + CHAR(13)+CHAR(10);
SET @strPrint = @strPrint + 'GO';
PRINT @strPrint;
        SET @sql = 'CREATE VIEW ['+ @tableName + '] WITH ENCRYPTION AS
SELECT      *
FROM      test.dbo. ' + @tableName + ' where ' + @clmn + ' IN(' +@comp1+ ',' + @comp2 + ')'
SET @sql = replace(@sql, '`', char(39));
print @sql
      EXEC SP_EXECUTESQL @sql
FETCH      NEXT FROM TableCursor
      INTO      @tableName
END
 Else
BEGIN
DECLARE @strPrint2 VARCHAR(100);
SET @strPrint2 = 'use ' + @DBNMDest;
SET @strPrint2 = @strPrint + CHAR(13)+CHAR(10);
SET @strPrint2 = @strPrint + 'GO';
PRINT @strPrint2;
      SET @sql = 'CREATE VIEW ['+ @tableName + '] WITH ENCRYPTION AS
SELECT      *
FROM      test.dbo.' + @tableName
print @sql
      EXEC SP_EXECUTESQL @sql
FETCH      NEXT FROM TableCursor
      INTO      @tableName
END
END
CLOSE TableCursor
DEALLOCATE TableCursor
/**
*  Clear up by deleting the Temp table
**/

DROP TABLE #Tables
/**
*  END
**/
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros