Creating stored procs with VB.NET

[ninelg]

I have to create databases and stored procs based on a user's prompt through a web page.

Is there a way to create a stored proc within VB.NET?

Thanks,
Ninel

[ninelg]

I need to create a stored procedure that has quite a few statements.

CREATE PROCEDURE usp_PoliticalProcessing 
AS

UPDATE dbo.list_staging 
SET sPhone = RTrim(LTrim(Convert(varchar(30), Convert(numeric(20, 1), phone))))

UPDATE dbo.list_staging 
SET sPhone = Substring(sphone, 1, patindex('%.%', sphone)-1)

UPDATE  dbo.list_staging 
SET  sphone =  replace(replace(replace(replace(replace(replace(sphone,'.',''),',',''  ),'-',''), ' ',''), '(', ''), ')', '')

ALTER TABLE  dbo.list_staging  ADD [iList_StagingID] INT IDENTITY(1,1)

ALTER TABLE  dbo.list_staging  ADD [sFailedValidation] char(1)

Update dbo.list_staging
SET sFailedValidation = 'X' 
WHERE(Len(RTrim(LTrim(sPhone))) <> 10)"

UPDATE a "
SET a.sFailedValidation = 'X' "
FROM  dbo.list_staging a "
INNER JOIN   dbo.list_staging b "
ON a.sPhone= b.sPhone "
WHERE(a.iList_StagingID > b.iList_StagingID)

I did some research and found that SQL Server doesn't allow statements to be combined in one batch. (You can combine them when using the GO keyword and executing from SQL Query Analyzer or another tool, but not from within code.)

Having said all that, how can I create this proc from within VB.NET?

Is this even possible?

Thanks,
Ninel

[szlamany]

Have you tried executing a SQL string with all that in it?

It should work.

GO is a keyword that QA recognizes to break up a batch - not to break up multiple lines.

[techgnome]

Start here:
http://davidhayden.com/blog/dave/arc...3/27/2890.aspx


scroll down to the SQL Server Management Objects section.... links to tutorials there. The code samples are in C#, but it doesn't take much to convert them to VB.NET.


-tg
OG Tay sez: dude.