I believe I gave you this SQL the other day.
First, in your
IF EXISTS
statement, you need to place the object in single quotes. You also can't put the schema name in it has well. If you query the
sys.objects
view, you will see why. Your schema is filtered on the
[schema_id]=SCHEMA_ID('dbo')
part of the condition.
IF EXISTS(SELECT * FROM sys.objecs WHERE name='BuildClientSchema' AND [schema_id]=SCHEMA_ID('dbo'))
DROP PROCEDURE dbo.BuildClientSchema
GO
If this statement is still giving you trouble, try this:
IF EXISTS(SELECT * FROM sys.objecs WHERE name='BuildClientSchema' AND [schema_id]=SCHEMA_ID('dbo'))
BEGIN
DROP PROCEDURE dbo.BuildClientSchema
END
GO
Now, the
IF EXISTS
statement is not apart of your stored procedure definition. So if you are adding this to a SQL Server Database project, This line needs to be removed. The Visual Studio SSDT will actually handle when to do a
CREATE
vs an
ALTER
when deploying a database.
Also, if you are using a database project or SSDT, you will need to add reference to the
master
database so that build tools knows where
sp_executesql
lives on the server. This link shows how to do that:
SSDT: unresolved reference to object [dbo].[sp_executesql].[
^]
If you are getting an error trying to execute the stored procedure, it is because you are using
EXE @sql
instead of
EXE sp_executesql @sql
. To understand this better, take a look at this article to better understand dynamic SQL:
Execute Dynamic SQL commands in SQL Server[
^]