I'm writing an application in Visual Basic .NET but the application will use a SQL backend.
If the user runs the application and the backend database is not set up, I want the user to select a SQL Database Instance, supply an administrator user ID (sa) and Password and then the application will connect to the SQL Instance via ADO.NET and Create the Database.
The app will then create a Database user for the application to use (so we don't have users using the Admin account,) and then create the Tables and Stored procedures in the database and grant execute permissions to the user on the stored procedures.
In order to do this I'm passing the necessary Transact SQL statements as a string into a SqlCommand object and executing.
I almost have this all down BUT I have a concern around the creation of stored procedures.
Simple stored procedures like:
CREATE PROCEDURE MySimpleSP @ID bigint AS SELECT * FROM MyTable WHERE (ID = @ID)
I can pass in as one line of text and I'm fairly sure it will work. However, I have procedures written using multi steps with transactions like this:
CREATE PROCEDURE MyComplexProcedure @UserName varchar(255), @ID Bigint OUTPUT AS DECLARE @TRANS varchar(100), @RecordID bigint SELECT @TRANS ='INSERT_NEW_USER' BEGIN TRANSACTION @TRANS INSERT INTO UsersTable (UserName) VALUES(@UserName) IF @@ERROR = 0 BEGIN SELECT @RecordID = SCOPE_IDENTITY() IF @@ERROR =0 BEGIN COMMIT TRANSACTION @TRANS SELECT @ID = @RecordID RETURN END END IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION @TRANS RAISERROR('Unable to Insert User',16,1) WITH LOG RETURN END
I'm not so sure about the effect of a lack of line breaks if I make it a single line of text, e.g. "...DECLARE @TRANS varchar, @RecordID bigint SELECT @TRANS ='INSERT_NEW_USER'..." is the SQL server going to throw an error?