Convert sql to procedure

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

Select allOpen 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

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen 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.

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

Bye, Olaf.

thank you both.

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.

@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.

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.

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.

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

thats good to hear, that was me not getting to grips with the best and assisted.

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..