How to use dynamic SQL

Question

0.00/5 (No votes)

See more:

Hi,
I am using dynamic sql to create a new schema and to create a table in that new created schem. I have written following stored procedure.

SQL

IF exists(select * from sys.objecs where name=dbo.BuildClientSchema and [schema_id]=SCHEMA_ID('dbo'))
drop procedure dbo.BuildClientSchema
go

CREATE PROCEDURE dbo.BuildClientSchema
@schemaname NVARCHAR(20)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'CREATE SCHEMA [' + @schemaName + '] AUTHORIZATION [dbo];'

PRINT @sql

EXEC @sql

SET @sql = 'CREATE TABLE [' + @schemaName + '].[Products](' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'Id INT PRIMARY KEY,' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'Name NVARCHAR(30) NOT NULL,' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'ProductDesc NVARCHAR(100),' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'Price DECIMAL (20),' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'CategoryId INT NOT NULL)'

		PRINT @sql

		EXEC @sql

END

It shows me error in if statement that the statement is not recognized - error - SQL70001. Help to resolve this.

Posted 17-Jun-15 18:10pm

Dolly Nimavat

Add a Solution

Comments

Om Prakash Pant 18-Jun-15 0:21am

I think in this statement,"dbo.BuildClientSchema" should be in single quotes as well.
select * from sys.objecs where name=dbo.BuildClientSchema ....

Dolly Nimavat 18-Jun-15 0:24am

I did it. Still the same error.

Dolly Nimavat 18-Jun-15 0:26am

I also found the answer that change build action property of the stored procedure. But in properties window there is no such property exists.

Abhipal Singh 18-Jun-15 11:53am

Build action property comes under visual studio .dbproj projects. Basically, it is used to specify if the sql file is to be considered for build or not.

Ignore that for now, if you are not using visual studio's database projects.

If you are using Visual studio projects then you need to omit the IF statement as it will not work there.
.dbproj uses a different strategy to achieve what you are trying to do using IF clause.
Read the link below to see how you can use database project settings:
https://msdn.microsoft.com/en-us/library/aa833291(v=vs.100).aspx

Also, there is an issue with you IF statement. please replace it with the statement below:
IF exists(select * from sys.objects where name='dbo.BuildClientSchema' and [schema_id]=SCHEMA_ID('dbo'))
drop procedure dbo.BuildClientSchema
go

Dolly Nimavat 18-Jun-15 12:34pm

I am not using database project.So now what can I do instead of using if statement?

2 solutions

Solution 2

Try this:

SQL

IF exists(select * from sys.objects where name='dbo.BuildClientSchema' and [schema_id]=SCHEMA_ID('dbo'))
drop procedure dbo.BuildClientSchema;
go

Your code had 1) sys.objecs instead of sys.objects 2) No quotes around procedure name

Posted 18-Jun-15 6:01am

Corporal Agarn

Comments

Dolly Nimavat 18-Jun-15 12:31pm

I tried it. Still the same error.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

virusstorm · Accepted Answer · 2015-06-18T06:07:00

Solution 3

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.

SQL

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:

SQL

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[^]

Posted 18-Jun-15 6:07am

virusstorm

Updated 18-Jun-15 7:17am

v3

Comments

Dolly Nimavat 18-Jun-15 12:29pm

Writing the statement - EXEC sp_executesql @sql - gives the error - 'the procedure has an unresolved reference to object [dbo].[sp_executesql]'

virusstorm 18-Jun-15 13:01pm

Are you doing this inside a SQL Server Database Project? If so, what version of Visual Studio are you using?

Dolly Nimavat 18-Jun-15 13:09pm

Yes. I am using VS2013 and sqlserver 2012

virusstorm 18-Jun-15 13:14pm

This will do the trick for you:
http://sqlserverselect.blogspot.com/2012/08/ssdt-unresolved-reference-to-object.html

Dolly Nimavat 18-Jun-15 13:25pm

But i can't understand why there is problem with if statement?

virusstorm 18-Jun-15 13:29pm

The IF statement is only if you were in a query window. You have to understand out the SQL Server Data Tools (SSDT) works behind the scenes. Under the hood, the tool is building a model of your database. It uses this model to resolve references and compare your model to a target database for deployment/publishing purposes. When you throw extra statements like the IF in front of a CREATE statement, the tool doesn't understand what you are trying to do. The whole point of that IF statement is to ensure that the stored procedure doesn't exist. In Visual Studio, SSDT is taking care of this for us, so we don't need it.

Dolly Nimavat 18-Jun-15 13:31pm

In my VS when I right click to reference folder, there is no - Add database reference - option.

Dolly Nimavat 18-Jun-15 13:36pm

Is it because i am not working on database project? If so, then what is the alternative?

virusstorm 18-Jun-15 21:04pm

What is the project type (database, forms, web, etc.) that is causing the reference error? I don't think we are on the same page.

Dolly Nimavat 18-Jun-15 23:24pm

To use sp_executesql, you gave me a link, in that solution it says to add database reference in solution explorer. When I right click in my solution explorer reference folder, there is no option like - add database reference. Only add reference and add service reference is there. But, with exec, the stored procedure works fine. The error is in the IF statement. Let me try to use stored procedure without if statement.

virusstorm 19-Jun-15 7:22am

You don't add references to a solution, you add them to a project. I'm trying to understand what project type you are using and how you are getting your error. From what I'm hearing, it sounds like you are not using the SQL I gave you correctly.

Dolly Nimavat 19-Jun-15 11:08am

I am using asp .net web forms project and your SQL code I have written in stored procedure. By the way, I solved the problem using 'exec (@sql)'.