Solved

Convert sql to procedure

Posted on 2016-08-15
15
63 Views
Last Modified: 2016-08-16
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
Comment
Question by:PeterBaileyUk
  • 5
  • 5
  • 4
  • +1
15 Comments
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 41756700
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41756706
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
 

Author Comment

by:PeterBaileyUk
ID: 41756708
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 41756710
>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
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
ID: 41756713
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
 

Author Comment

by:PeterBaileyUk
ID: 41756716
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756718
That's a misleading error, but you miss to put stored proc code inside BEGIN...END.

Bye, Olaf.
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41756719
thank you both.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756747
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41756754
@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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756790
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
 

Author Comment

by:PeterBaileyUk
ID: 41757395
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41757763
Olaf and I worked it out behind the scenes, so everything's good now.
0
 

Author Comment

by:PeterBaileyUk
ID: 41757779
thats good to hear, that was me not getting to grips with the best and assisted.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41757881
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

713 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