[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Trying to execute multiple t-sql statements in single t-sql call in vb.net

Posted on 2013-01-25
7
Medium Priority
?
227 Views
Last Modified: 2013-01-30
The below code is failing with the following error

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'CREATE'.
Must declare the scalar variable "@CogsdaleId".
Incorrect syntax near 'GO'.

The t-sql code works in mssql server, but not when executed in vb using the following code

   Dim results As System.Data.SqlClient.SqlDataReader
            Dim connect As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
            Dim callFunctionStr As New System.Data.SqlClient.SqlCommand()


            connect.ConnectionString = Application("DefaultConnectionString")
            callFunctionStr.Connection = Session("conn")

            callFunctionStr.CommandText = My.Resources.getcurbalance
            callFunctionStr.Connection.Open()

            results = callFunctionStr.ExecuteReader

Open in new window


This is the contents of the resource file (which is a .sql file)

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'Cogsdale_getMostRecentBill') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE Cogsdale_getMostRecentBill
END
GO

CREATE PROCEDURE [dbo].[Cogsdale_getMostRecentBill]
	@CogsdaleId		varchar(13)
AS
DECLARE @FID	INT
DECLARE @SID	INT
DECLARE	@SDATE	DATETIME

DECLARE @tmpStatements TABLE
(
	fld_ID	INT	IDENTITY,
	CogsdaleId	char(13),
	StatementNumber	int,
	StatementDate	datetime,
	BilledAMT	float,
	OutstandingAMT	float,
	DueDate	datetime,
	Status	char(8)
)

INSERT INTO @tmpStatements(CogsdaleID,StatementNumber,StatementDate,BilledAMT)
SELECT TOP 1
	RTRIM(B.umLocationID) + '-' + RTRIM(A.CUSTNMBR) AS CogsdaleId,
	A.umStatementDocNo AS StatementNumber,
	C.umDocDate AS StatementDate,
	A.umStatementAmount AS BilledAMT
FROM UM00701 AS A
	inner join UM00600 AS B ON A.umLocationID = B.umLocationID
	inner join um00702 AS D ON A.umStatementDocNo = D.umStatementDocNo
	inner join UM42300 AS C ON D.umDocumentNumber = C.umDocumentNumber
WHERE A.CUSTNMBR = RIGHT(@CogsdaleId,6)	AND C.umDocType IN (5,15,14) AND NOT C.umDocLoc = 1 AND B.umLocationID = LEFT(@CogsdaleId,6)
ORDER BY A.umLocationID, A.umStatementDate DESC

DECLARE curBill CURSOR FOR
	SELECT fld_ID,StatementNumber,StatementDate FROM @tmpStatements

OPEN curBill
FETCH NEXT FROM curBill
INTO @FID,@SID,@SDATE

	WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @outstandingAMT	float
			SELECT @outstandingAMT = ISNULL(SUM(umOutstandingAmt), 0.00)
			FROM
						(SELECT umOutstandingAmt
						FROM UM20400
						WHERE umDocumentNumber IN (SELECT umDocumentNumber FROM UM00702 WHERE umStatementDocNo = @SID)
						union 
						(select BilledAMT from @tmpStatements)) Docs
			
			IF @outstandingAMT = 0
				BEGIN
					UPDATE @tmpStatements SET OutstandingAMT = @outstandingAMT, Status='PAID' WHERE StatementNumber = @SID
				END
			ELSE IF @outstandingAMT > 0
				BEGIN
					IF GETDATE() > DATEADD(dd,30,@SDATE) AND @FID <> 1
						BEGIN
							UPDATE @tmpStatements SET OutstandingAMT = @outstandingAMT, Status='PAST DUE' WHERE StatementNumber = @SID
						END
					ELSE
						BEGIN
							UPDATE @tmpStatements SET OutstandingAMT = @outstandingAMT, Status='PAY' WHERE StatementNumber = @SID
						END
				END

			UPDATE @tmpStatements SET DueDate = DATEADD(dd,30,@SDATE) WHERE StatementNumber = @SID

			FETCH NEXT FROM curBill
			INTO @FID,@SID,@SDATE
		END
CLOSE curBill
DEALLOCATE curBill
SELECT CogsdaleId,StatementNumber,StatementDate,BilledAMT,OutstandingAMT,DueDate,Status FROM @tmpStatements
GO

Open in new window

0
Comment
Question by:UnderSeven
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38819649
The CREATE PROCEDURE statement isn't formatted correctly - it's missing some perentheses. Lines 7-9 should be:

CREATE PROCEDURE [dbo].[Cogsdale_getMostRecentBill] (
	@CogsdaleId		varchar(13)
) AS

Open in new window

0
 

Author Comment

by:UnderSeven
ID: 38819716
added suggested paranths, no change in error, made sure changes were being accounted for in execution.
0
 

Author Comment

by:UnderSeven
ID: 38819796
Ah got it.  The problem seems to be I can't create procs in statement files that include preceeding statements.

If anyone know of a way I could do that then I'd love to hear.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Accepted Solution

by:
UnderSeven earned 0 total points
ID: 38820164
Turns it out was the use of Go without the use of semi colins.  This works:

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'Cogsdale_getActiveConnections') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE Cogsdale_getActiveConnections
END;

CREATE PROCEDURE [dbo].[Cogsdale_getActiveConnections]
	@CogsdaleID	char(13)
AS . . . ect

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38823066
Ryan,

The CREATE PROCEDURE statement isn't formatted correctly - it's missing some perentheses. Lines 7-9 should be:

If you are referring to the parenthesis around the parameters they are optional.  See here from SQL Server BOL:
Syntax
 
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name
 
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38824964
acperkins: You're right - I've always used the perentheses and didn't realize they were optional. That's what I get for rushing through :(
0
 

Author Closing Comment

by:UnderSeven
ID: 38834555
This was the only solution that solved the problem.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question