(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

"string data right truncation"

Hello everyone,

I'm using CRecordset in order to write into the SQL Server, i have a problem that

everytime i'm trying to write a varchar fields with size X and i put in the CString larger

string then X i get CDBException.

My question is if there is anyway to to tell the CRecordset to truncate automatialy the strings

instead of doing it manually for every record i write.

Thanks

check this

create table #tt(a varchar(5))

insert #tt select '1234567' --fails

insert #tt select left('1234567',5) -- succeeds

insert #tt select substring('1234567',1,5) -- succeeds

Madhu

|||

10x for the answer, but as far as i know this solution can't help me whilst using CRecordset

This is an example code(c++) of how i use the recordset:

---

class CJREventsArchiveRec : public CRecordset {

....

CString m_Bla;

...

}

The table in this example include a single nvarchar field(Bla) with size 5;

m_eventRec.AddNew();

m_Bla = "123456";

m_eventRec.Update(); // This row cause CDBException (string data right truncation).

---

of course in this sample its easy to manually avoid this problem,but its no that easy at my app.

The Solution i'm looking for is somehow to tell the Recordset to automatically truncate the string as much as he need, and stop throwing on me Exceptions

|||

That would mean silent data loss and I don't think the ODBC driver would silently allow data loss. Note that on the way out, it might truncate silently but when data is going to the server, it won't.

Thanks

Waseem