Help: New to SProcs - SQL Server Forums

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)

Help: New to SProcs

Author

Topic

gundavarapu
Starting Member

8 Posts

Posted - 2003-07-23 : 06:49:53

Hi there,

Can any one tell what is wrong with this code.

CREATE PROCEDURE dbo.AddBookCopies
(
@strBookId varchar(10),
@dtEntryDate datetime,
@intNumberOfBooks int,
)
As
declare @intAvailableId int
SELECT @intAvailableId = count(strBookid)
FROM LibBookCopies
WHERE strBookId = @strBookId

declare @iCtr int
set @iCtr=1

while (@iCtr <= @intNumberofBooks)
Begin
If (@intAvailableId >= 9)
Begin
INSERT INTO LibBookCopies values (
@strBookId,
@strBookId + "_" + (intAvailableId + 1),
'Available',
@dtEntryDate)
Else
INSERT INTO LibBookCopies values (
@strBookId,
@strBookId + "_0" + (intAvailableId + 1),
'Available',
@dtEntryDate)
End

set @intAvailableId=@intAvailable + 1
End
GO

I believe there would be loads of errors in this code.

Thanks,

Regards,
Sidhu

joseph
Starting Member

10 Posts

Posted - 2003-07-23 : 07:58:42

CREATE PROCEDURE dbo.AddBookCopies
(
@strBookId varchar(10),
@dtEntryDate datetime,
@intNumberOfBooks int],)
As
declare @intAvailableId int
SELECT @intAvailableId = count(strBookid)
FROM LibBookCopies
WHERE strBookId = @strBookId

declare @iCtr int
set @iCtr=1

while (@iCtr <= @intNumberofBooks)
Begin
If (@intAvailableId >= 9)
Begin
INSERT INTO LibBookCopies values (
@strBookId,
@strBookId + "_" + (@intAvailableId + 1),
'Available',
@dtEntryDate)
end
Else
INSERT INTO LibBookCopies values (
@strBookId,
@strBookId + "_0" + (intAvailableId + 1),
'Available',
@dtEntryDate)
End

set @intAvailableId=@intAvailable + 1
End
GO

And here is the correct version

CREATE PROCEDURE dbo.AddBookCopies
(
@strBookId varchar(10),
@dtEntryDate datetime,
@intNumberOfBooks int
)
As
declare @intAvailableId int
SELECT @intAvailableId = count(strBookid)
FROM LibBookCopies
WHERE strBookId = @strBookId

declare @iCtr int
set @iCtr=1

while (@iCtr <= @intNumberofBooks)
Begin
If (@intAvailableId >= 9)
Begin
INSERT INTO LibBookCopies values (
@strBookId,
@strBookId + '_' + (@intAvailableId + 1),
'Available',
@dtEntryDate)
end
Else
INSERT INTO LibBookCopies values (
@strBookId,
@strBookId + '_0' + (@intAvailableId + 1),
'Available',
@dtEntryDate)

set @intAvailableId=@intAvailableId + 1
End
GO

gundavarapu
Starting Member

8 Posts

Posted - 2003-07-23 : 09:10:20

Thanks for the reply,
even the sproc, which you gave is not working, i thought if conditional statement with s single statement need not have a begin...end.

why is it not working for me.

I have changed the code and now it gives the following error.

CREATE PROCEDURE dbo.AddBookCopies
(
@strBookId VARCHAR(10),
@dtEntryDate DATETIME,
@intNumberOfBooks INT
)
AS
DECLARE @intAvailableId INT

SELECT @intAvailableId = COUNT(strBookid)
FROM LibBookCopies
WHERE strBookId = @strBookId

DECLARE @iCtr INT
SET @iCtr=1

WHILE (@iCtr <= @intNumberofBooks)
BEGIN
IF (@intAvailableId >= 9)
BEGIN
INSERT INTO LibBookCopies VALUES (
@strBookId,
@strBookId & "_" & STR(intAvailableId + 1),
"Available",
@dtEntryDate)
END
ELSE
BEGIN
INSERT INTO LibBookCopies VALUES (
@strBookId,
@strBookId & "_0" & STR(intAvailableId + 1),
"Available",
@dtEntryDate)
END

SET @intAvailableId=@intAvailableId + 1
END

GO

Errors:

No 128, The name "_" is not permitted in this context
No 128, The name "_0" is not permitted in this context

Thanks

Regards,
Sidhu

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-23 : 09:54:38

Maybe you should be using single quotes ' not double quotes " for your string literals?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 16:19:11

What's with the &? This aint access...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 16:21:16

Instead of &, use + and instead of double quotes use single quotes:

CREATE PROCEDURE dbo.AddBookCopies
(
@strBookId VARCHAR(10),
@dtEntryDate DATETIME,
@intNumberOfBooks INT
)
AS
DECLARE @intAvailableId INT

SELECT @intAvailableId = COUNT(strBookid)
FROM LibBookCopies
WHERE strBookId = @strBookId

DECLARE @iCtr INT
SET @iCtr=1

WHILE (@iCtr <= @intNumberofBooks)
BEGIN
IF (@intAvailableId >= 9)
BEGIN
INSERT INTO LibBookCopies VALUES (
@strBookId,
@strBookId + '_' + STR(intAvailableId + 1),
'Available',
@dtEntryDate)
END
ELSE
BEGIN
INSERT INTO LibBookCopies VALUES (
@strBookId,
@strBookId + '_0' + STR(intAvailableId + 1),
'Available',
@dtEntryDate)
END

SET @intAvailableId=@intAvailableId + 1
END

GO

Tara

Edited by - tduggan on 07/23/2003 16:21:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources