Convert sql to procedure

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

PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zephyr_hex (Megan)DeveloperCommented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
PeterBaileyUkAuthor 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

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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

0
Olaf DoschkeSoftware DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor 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.
0
Olaf DoschkeSoftware DeveloperCommented:
That's a misleading error, but you miss to put stored proc code inside BEGIN...END.

Bye, Olaf.
0
PeterBaileyUkAuthor Commented:
thank you both.
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
@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.
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
PeterBaileyUkAuthor 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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Olaf and I worked it out behind the scenes, so everything's good now.
0
PeterBaileyUkAuthor Commented:
thats good to hear, that was me not getting to grips with the best and assisted.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.