Stored Procedure Variable Problem

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?
LVL 13
TorrwinAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.