Convert sql to procedure

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
I have a query that works perfectly if i execute, made of several actions, I am having trouble making it into a stored procedure. My ultimate aim is to run this from a VB app.

I cannot get the syntax right.

use Dictionary
go

WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NULL)

UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition

			FOR XML PATH('')))


use Dictionary
go

WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShorttag
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NOT NULL)

UPDATE tblwords
SET StrShorttag = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition

			FOR XML PATH('')))

use Dictionary
go
update TblWords
set StrShort=Rtrim(REPLACE(StrShort,' ',''))

update TblWords
set StrShort=LTRIM(REPLACE(StrShort,' ',''))

update TblWords
set StrShortTag=Rtrim(REPLACE(StrShortTag,' ',''))

update TblWords
set StrShortTag=LTRIM(REPLACE(StrShortTag,' ',''))

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
You shouldn't need to do anything special.  Your structure to create the stored procedure should look something like:

USE [myDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]
AS
BEGIN
  /* Queries go here */
END
GO

Open in new window



And then you'd call the name of your stored procedure from your VB.  In the case above, the stored procedure is called myStoredProcedure
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
The issue is likely the repeated use of GO, which acts as a batch execution statement that executes everything above it (or since the last GO) and then everything is removed from memory.  To resolve, remove all GO's except for the last one.
use Dictionary
go

CREATE PROCEDURE your_proc_name AS 

/*
Meaningful code comments here
*/

WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NULL)
UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition
			FOR XML PATH('')))

WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShorttag
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NOT NULL)
UPDATE tblwords
SET StrShorttag = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition
			FOR XML PATH('')))

update TblWords
set StrShort=Rtrim(REPLACE(StrShort,' ',''))

update TblWords
set StrShort=LTRIM(REPLACE(StrShort,' ',''))

update TblWords
set StrShortTag=Rtrim(REPLACE(StrShortTag,' ',''))

update TblWords
set StrShortTag=LTRIM(REPLACE(StrShortTag,' ',''))
GO

Open in new window

Author

Commented:
I did that first i will try again in case i missed something, it didnt work it says:

Msg 319, Level 15, State 1, Procedure usp_CreateStrStringsplusTidyup, Line 23 [Batch Start Line 8]
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.


USE Dictionary
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_CreateStrStringsplusTidyup]
AS
BEGIN
 
 WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NULL)

UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition

			FOR XML PATH('')))




WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShorttag
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NOT NULL)

UPDATE tblwords
SET StrShorttag = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition

			FOR XML PATH('')))


update TblWords
set StrShort=Rtrim(REPLACE(StrShort,' ',''))

update TblWords
set StrShort=LTRIM(REPLACE(StrShort,' ',''))

update TblWords
set StrShortTag=Rtrim(REPLACE(StrShortTag,' ',''))

update TblWords
set StrShortTag=LTRIM(REPLACE(StrShortTag,' ',''))


END
GO

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>Incorrect syntax near the keyword 'with'.
Ok.  One of the goofyriffic quirks of T-SQL, and designed surely only so that Microsoft can have stupid questions on certification exams, is that you need a semi-colon immediately before any CTE..WITH statements.

So adding that..
use Dictionary
go

CREATE PROCEDURE your_proc_name AS 

/*
Meaningful code comments here
*/

;WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NULL)
UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition
			FOR XML PATH('')))

;WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShorttag
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NOT NULL)
UPDATE tblwords
SET StrShorttag = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition
			FOR XML PATH('')))

update TblWords
set StrShort=Rtrim(REPLACE(StrShort,' ',''))

update TblWords
set StrShort=LTRIM(REPLACE(StrShort,' ',''))

update TblWords
set StrShortTag=Rtrim(REPLACE(StrShortTag,' ',''))

update TblWords
set StrShortTag=LTRIM(REPLACE(StrShortTag,' ',''))
GO

Open in new window

Software Developer
Commented:
St Jimbo is on the right track.

1. You also don't need the final GO.

2. What you need is to end statements before each WITH (CTE) expression with the otherwise optional semicolon. A CTE does not need to be top of a batch of commands, but it needs to be unequivocally the start of a command.

Bye, Olaf.

Author

Commented:
I just read another experts comment

still no joy
use Dictionary
go

CREATE PROCEDURE [dbo].[usp_CreateStrStringsPlusTidyup] AS 

/*
Create StrStrings and tidy up after
*/

WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NULL)
UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition
			FOR XML PATH('')))

WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShorttag
    FROM tblwords w
		LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
    WHERE wt.word IS NOT NULL)
UPDATE tblwords
SET StrShorttag = RTRIM((SELECT I.Word + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition
			FOR XML PATH('')))

update TblWords
set StrShort=Rtrim(REPLACE(StrShort,' ',''))

update TblWords
set StrShort=LTRIM(REPLACE(StrShort,' ',''))

update TblWords
set StrShortTag=Rtrim(REPLACE(StrShortTag,' ',''))

update TblWords
set StrShortTag=LTRIM(REPLACE(StrShortTag,' ',''))
GO

Open in new window


the error message is Msg 319, Level 15, State 1, Procedure usp_CreateStrStringsPlusTidyup, Line 20 [Batch Start Line 2]
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Olaf DoschkeSoftware Developer

Commented:
That's a misleading error, but you miss to put stored proc code inside BEGIN...END.

Bye, Olaf.

Author

Commented:
thank you both.
Olaf DoschkeSoftware Developer

Commented:
Just to make one thing more clear: As Jimbo said GO is a batch. It's not a T-SQL command, but a meta command of SSMS to send everything up to a GO within one request to SQL Server. So you can write multiple batches of commands (which each might have several queries) in one Query window and execute that all. Some things need to be at the top of a batch.

One of the goofyriffic quirks of T-SQL, and designed surely only so that Microsoft can have stupid questions on certification exams, is that you need a semi-colon immediately before any CTE..WITH statements.

That's unfortunately what I see people write, but is wrong. The error message actually hints a WITH (cte) command can be confused witha WITH XML clause. Therefore to make that unequivocally, you end the previous command with a semicolon. Even if you do a semicolon right before WITH just means you end no command, like two semicolons in C+ code also will work, though I dislike this solution, as it disregards the why.

Categorizing this as goofy or - I like this word invention goofyriffic - is just stating not understanding the reason is simply the bad choice of the command name starting with WITH as that already is a key word of other query clauses.

I prefer a) ending all commands with semicolon and b) in case the CTE is the first command of a storec proc, make that clear via using BEGIN, that works in the smae way, it makes clear the WITH really means the start of a CTE.

Bye, Olaf.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
@Olaf - Since I've nailed both issues that stood between this expert's previous code and a working solution, and answer(s) have been accepted, what exactly is the purpose of writing the above essay?   You've already been awarded Best Answer and 1,000 points for essentially agreeing with my earlier comment.
Olaf DoschkeSoftware Developer

Commented:
I think we settle this in private messages.

And sorry, Peter, let me correct one thing: WITH XML is not the other WITH clause this could be confused with, but other WITH clauses, eg table hints. While the T-SQL parser is normally fine detecting command ending and starting, it is confused whether WITH really means a CTE.

Bye, Olaf.

Author

Commented:
As the poster of the question I believed I was sharing the points equally, which appears not to be the case.

St Jimbos post assisted with the structure but it still came back with an error, then the addition of the semicolon solved it and the SQL ran.

I was very thankful for both posts and hoped they got equal share of points.

if that's not the case then maybe an admin can open the question and tell me how I distribute equally.

as to the additional background info well as a rookie to sql server I of course read and am grateful.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Olaf and I worked it out behind the scenes, so everything's good now.

Author

Commented:
thats good to hear, that was me not getting to grips with the best and assisted.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
EE changed their question points rules recently so that its easier to split points across all comments that helped, but not as intuitive to alter the point split across those comments.  We're still trying to feel that out..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial