Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Unanswered: (newbie) - Stored Procedure Problem

    Can anyone tell me what is wrong with this stored procedure?

    Code:
    CREATE  PROCEDURE [dbo].[AddGroupPermission]
    	@Perm varchar(16)
    AS
    	ALTER TABLE tblUserGroups ADD @Perm VARCHAR(1) NULL
    GO
    When I click on Check Syntax, I get 'Error 170 - Line 4: Incorrect syntax near @Perm'

    I have checked the syntax for the ALTER command, and it looks correct to me...

    This is my first day at using SQL Server in anger, so any help appreciated
    Mark Wills.

  2. #2
    Join Date
    Apr 2003
    Posts
    12
    You must use dynamic sql for that, check out :

    http://www.sqlteam.com/item.asp?ItemID=4599

    The following will work

    CREATE PROCEDURE [dbo].[AddGroupPermission]
    @Perm varchar(16)
    AS
    exec ('ALTER TABLE tblUserGroups ADD ' + @Perm + 'VARCHAR(1) NULL')
    GO

  3. #3
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25
    Oh wow! Thanks for that - I didn't even know you could do that directly in SQL server - I'm used to doing it in ASP pages, of course, but this DB is new to me

    I'm off to check your link now - thanks very much for taking the time to post.

    Mark.
    Mark Wills.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •