Creating stored procs with VB.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Creating stored procs with VB.NET

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-16 : 14:24:50

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 14:31:15

Yes. You just use ExecuteNonQuery.

cnSql.Open()
cmdSqlScript.ExecuteNonQuery()
cnSql.Close()

Tara Kizer

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-16 : 16:07:58

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 16:27:00

quote:
Originally posted by ninel

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

You just need to put the entire stored procedure into the cmd.CommandText.

Like this:

cmdSqlScript.CommandText = YourStoredProcGoesHere
cmdSqlScript.CommandType = CommandType.Text
cmdSqlScript.Connection = cnSql

Tara Kizer

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-16 : 16:30:19

I did that. It is erroring out.

Have you done this before?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 16:33:40

Yes. I've got an application the reads the contents of a file and executes the contents on a SQL Server. The file can contain stored procedures, views, tables, queries, etc...

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources