Sunday, March 25, 2012

(newbie) - Stored Procedure Problem

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

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 :)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|||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.sql

No comments:

Post a Comment