Creating a DECLARE variable with SMO

How can I create this stored proc with the SMO StoredProcedure class?

It doesn't want to work and I think it has something to do with the DECLARE statement. Anyone know how I can get this to work

Cheers
Jon

storedProc.TextBody = "DECLARE @.GroupID int"+

"SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")"+

"INSERT INTO gworkshop.Users"+

"(GroupID, Username, Password, Active, Deleted)"+

"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";

What does the rest of your code to create the procedure look like? Are you trying to create it and it's failing? If so, what's the error message?|||

I'm not in work right now so can't paste the code but basically its creating a server then the database name then in the storedprocedure I add parameters like this

StoredProcedure.Parameters.Add(new StoredProcedureParameter(storedprocedure,"@.Name", DataType.VarChar(30)));

The error is something like "There was an error creating stored procedure", then the procedure name

|||

If you are debugging, you can break on the exception it's throwing, then look at the inner exception(s) of that exception to find out exactly what the server is not liking about your stored procedure. Also, you can look at the script of the stored procedure ( use the script function), and look at the syntax it generates for you.

Whenever I create a stored procedure, I always do it like so:

Microsoft.SqlServer.Management.Smo.StoredProcedure proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure(database, procName);

proc.AnsiNullsStatus = true;

proc.QuotedIdentifierStatus = true;

proc.TextMode = false;

// Insert Params

Microsoft.SqlServer.Management.Smo.StoredProcedureParameter param = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(proc, "@." + paramName, paramDataType);

// If it's an output

param.IsOutputParameter = true;

proc.Parameters.Add(param);

// Now for the body

proc.TextMode = true;

proc.TextBody = "SELECT * FROM foo";

proc.Create();

|||

It seems that the problem was with the DECLARE @.GroupID

You need to add any parameters to the stored procedure, you can't add them in the SQL

storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Username", DataType.VarChar(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Password", DataType.Binary(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Active", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Deleted", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.GroupID", DataType.Int));

//storedProc.TextBody = "DECLARE @.GroupID int" +
storedProc.TextBody = "SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")" +
"INSERT INTO gworkshop.Users" +
"(GroupID, Username, Password, Active, Deleted)" +
"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";
storedProc.Create();