Create Stored Procedure through ADO.NET

[G_Waddell]

HI All,

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?

[BitBlt]

No, SQL Server doesn't care about line breaks. Line breaks are for readability only.

However, having the string built in your app like this:

mySQLString = "create procedure dbo.myProc"
mySQLString = mySQLString + " @inParm varchar(10), @outParm varchar(10)"
mySQLString = mySQLString + " AS"
mySQLString = mySQLString + " begin..."
blah, blah, blah...

allows you to have your "no line breaks" and also have some degree of readability. You just have to make sure there are spaces in the proper places, and proper punctuation (commas, quote marks doubled up as needed, etc.).

Hope this helps! Good luck!

[G_Waddell]

Thanks!!