what s wrong with this small SP pls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

what s wrong with this small SP pls

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-10-03 : 10:06:54

ALTER PROCEDURE [dbo].[GetDocumentsByLike]
-- Add the parameters for the stored procedure here
@KeyWord varchar(500),
@FormOrProc varchar(3),
@Instruction int
AS
BEGIN
SET NOCOUNT ON;
declare @type char(1);
set @type=0;
create table #Types(type char(1))
while (@type is not null)
set @type = substring(@FormOrProc,0,1)
insert into #Types values(@type)
END
drop table #Types

select * from #Types
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

I get error at the last END:
Incorrect syntax near 'END'

Thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 10:10:48

quote:
Originally posted by rtutus

ALTER PROCEDURE [dbo].[GetDocumentsByLike]
-- Add the parameters for the stored procedure here
@KeyWord varchar(500),
@FormOrProc varchar(3),
@Instruction int
AS
BEGIN
SET NOCOUNT ON;
declare @type char(1);
set @type=0;
create table #Types(type char(1))
while (@type is not null)
BEGIN
set @type = substring(@FormOrProc,0,1)
insert into #Types values(@type)
END
drop table #Types

select * from #Types
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

I get error at the last END:
Incorrect syntax near 'END'

Thank you

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-03 : 10:13:16

had you realised you've also dropped the temp table before selecting from it?

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 10:13:34

Also, you are dropping the #Types table BEFORE fetching the resultset from it...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 10:17:04

Here is your code

ALTER PROCEDURE dbo.GetDocumentsByLike
(
	@KeyWord VARCHAR(500),
	@FormOrProc VARCHAR(3),
	@Instruction INT
)
AS

SET NOCOUNT ON

DECLARE	@Type CHAR(1)

SET @Type = ''

CREATE TABLE	#Types
		(
			Type CHAR(1)
		)

WHILE @Type IS NOT NULL
	BEGIN
		SET @Type = SUBSTRING(@FormOrProc, 0, 1)

		INSERT	#Types
		VALUES	(
				@Type
			)
	END

DROP TABLE #Types

SELECT	Type
FROM	#Types

1) @Type never changes!
2) You are fetching same characters in an eternal loop!
3) SUBSTRING(.., 0, 1) should be SUBSTRING(..., @x, 1)
4) @KeyWord is not used.
5) @Instruction is not used.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 10:19:22

And since @FormOrProc is only three characters long, this can replace all code above

ALTER PROCEDURE dbo.GetDocumentsByLike
(
	@KeyWord VARCHAR(500),
	@FormOrProc VARCHAR(3),
	@Instruction INT
)
AS

SET NOCOUNT ON

SELECT	Type
FROM	(
		SELECT	SUBSTRING(@FormOrProc, 1, 1) AS Type UNION ALL
		SELECT	SUBSTRING(@FormOrProc, 2, 1) AS Type UNION ALL
		SELECT	SUBSTRING(@FormOrProc, 3, 1) AS Type
	) AS d
WHERE	Type > ''

E 12°55'05.25"
N 56°04'39.16"

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-10-03 : 10:33:51

Thanks guys for all these suggestions that are very helpful to me.
But unless I missread, I think none of this fixes the error: Incorrect syntax near 'END'

which prevents me from even creating the SP

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-03 : 10:35:26

the first reply (from harsh) answered it for you. you are missing a 'begin'

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 10:39:35

Think you neeed this

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 05:10:22

quote:
Originally posted by rtutus

Thanks guys for all these suggestions that are very helpful to me.
But unless I missread, I think none of this fixes the error: Incorrect syntax near 'END'

which prevents me from even creating the SP

Post the modified procedure

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-04 : 09:56:51