Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

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

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 5
  • 5
  • 4
  • +1
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now