Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Stored Procedure Variable Problem

Posted on 2007-11-27
1
260 Views
Last Modified: 2010-03-19
Hello, I have the following stored procedure that is giving me the "Must declare the scalar variable @createquery" error supposedly at line 2:

--Deletes the table if it already exists and re-creates it using the users SQL
CREATE PROCEDURE dbo.usp_CreateTable
      @tablename nvarchar(128) = NULL,  --The name of the table to create
      @createquery nvarchar(500) = NULL --The sql statement to create the table
AS
      DECLARE @msg AS NVARCHAR(500);

      --Input Validation
      IF @tablename IS NULL
            BEGIN
                  SET @msg = N'A value must be supplied for parameter @tablename.';
                  RAISERROR(@msg, 16, 1);
                  RETURN;
            END

      IF @createquery IS NULL
            BEGIN
                  SET @msg = N'A value must be supplied for parameter @createquery.';
                  RAISERROR(@msg, 16, 1);
                  RETURN;
            END

      IF OBJECT_ID(@tablename) IS NOT NULL
            DECLARE @DropSQL nvarchar(255)
            SET @DropSQL='DROP TABLE [dbo].' + @tablename
            EXEC(@DropSQL);
      GO

      EXEC(@createquery);
GO

What do I need to change to fix the proc?
0
Comment
Question by:Torrwin
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20361086
you have a GO too early... apart from the that you have another "problem" with the last IF ... the begin/end are missing:
--Deletes the table if it already exists and re-creates it using the users SQL
CREATE PROCEDURE dbo.usp_CreateTable
      @tablename nvarchar(128) = NULL,  --The name of the table to create
      @createquery nvarchar(500) = NULL --The sql statement to create the table
AS
BEGIN
      DECLARE @msg AS NVARCHAR(500); 
      --Input Validation
      IF @tablename IS NULL
            BEGIN
                  SET @msg = N'A value must be supplied for parameter @tablename.';
                  RAISERROR(@msg, 16, 1);
                  RETURN;
            END 
      IF @createquery IS NULL
            BEGIN
                  SET @msg = N'A value must be supplied for parameter @createquery.';
                  RAISERROR(@msg, 16, 1);
                  RETURN;
            END 
      IF OBJECT_ID(@tablename) IS NOT NULL
      BEGIN
            DECLARE @DropSQL nvarchar(255)
            SET @DropSQL='DROP TABLE [dbo].' + @tablename
            EXEC(@DropSQL);
      END 
      EXEC(@createquery);
END

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question