table datatypes params for stored procs

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

SQL Server Development (2000)

table datatypes params for stored procs

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-02-24 : 18:39:33

this might be a extremly dumb question but

can you have a table datatypes as the parmater for a stored procedure cant find a straight answer in BOL (not sure if i am looking in the right spots )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-24 : 18:46:51

I've never done it, but according to BOL you can (CREATE PROC topic):

quote:

data_type

Is the parameter data type. All data types, including text, ntext and image, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

What problem are you trying to solve as this doesn't seem like a good idea?

Tara

PW
Yak Posting Veteran

95 Posts

Posted - 2005-02-24 : 19:05:48

No, it is not supported.
A table variable only has scope within the code block where it is declared, and can't be passed into the scope of a stored procedure as an input parameter.
Use a temp table instead of the table variable.

Ex
Posting Yak Master

166 Posts

Posted - 2005-02-24 : 19:05:55

ALTER PROCEDURE UPDATESIZE (
@inputTable TABLE (id int IDENTITY(1,1), ParentID int,ChangeInSize int,SizingModeChange int)
)
AS

the error is

Incorrect syntax near the keyword 'TABLE'.

but i though my table declaration is fine

my problem :) is that someone designed this database row based :(

they have a procedure with a cursor calling another with a cursor that is a recrusvie YUK :) takes far to long far far

for a bit of a laugh here is the design any suggestions would be great

i am at the point where i might just redesign the lot :)

CREAATE PROCEDURE CHANGEENTITYSIZE (
@NewSizeOfEntity int,
@OldSizeOfEntity int,
@NewEntityID int)
AS
set nocount on
DECLARE @exceptionOccurred int
BEGIN TRAN
DECLARE @TypeChangeInSize int
SET @TypeChangeInSize = @NewSizeOfEntity - @OldSizeOfEntity
/*updateSize of all entityID from entity_item*/
DECLARE entityCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT Distinct EntityID FROM ENTITY_ITEM
WHERE TypeID = @NewEntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0
OPEN entitycursor
DECLARE
@ParentID int

FETCH NEXT FROM entityCursor INTO @ParentID
WHILE @@fetch_status = 0
BEGIN
EXEC @exceptionOccurred = UpdateSize @ParentID, @TypeChangeInSize, 0
IF(@exceptionOccurred = -1)/*if error occured in updateSize raise error, rollback and return*/
BEGIN
raiserror(50004,17,1)
ROLLBACK TRAN
RETURN
END

FETCH NEXT FROM entityCursor INTO @ParentID
END
CLOSE entityCursor
DEALLOCATE entityCursor
COMMIT
go

CREATE PROCEDURE UPDATESIZE (
@ParentID int,
@ChangeInSize int,
@SizingModeChange int)
AS
set nocount on
DECLARE
@AutoSize int,
@CurrSpares int

/* Determine if the new item's parent is auto sizing. */
SELECT @AutoSize = AutoSize FROM ENTITY
WHERE EntityID = @ParentID AND MajorBuildNo = 0 AND MinorBuildNo = 0

/* Update the size of the new item's parent. */
IF (@AutoSize = 1)
BEGIN
IF (@SizingModeChange = 1)
BEGIN
UPDATE ENTITY
SET SizeOfEntity = SizeOfEntity + @ChangeInSize
WHERE EntityID = @ParentID AND MajorBuildNo = 0 AND MinorBuildNo = 0
END
ELSE
BEGIN
UPDATE ENTITY
SET SizeOfEntity = SizeOfEntity + @ChangeInSize,
AutoSizeOfEntity = AutoSizeOfEntity + @ChangeInSize
WHERE EntityID = @ParentID AND MajorBuildNo = 0 AND MinorBuildNo = 0

END
END
ELSE
BEGIN
SELECT @CurrSpares = SparesInBytes FROM ENTITY
WHERE EntityID = @ParentID AND MajorBuildNo = 0 AND MinorBuildNo = 0

IF ((@CurrSpares - @ChangeInSize) < 0)
BEGIN
RETURN -1 /*returning -1 raises error 50004 and rollsback tran from the calling trigger or stored proc*/
END

UPDATE ENTITY
SET AutoSizeOfEntity = AutoSizeOfEntity + @ChangeInSize,
SparesInBytes = SparesInBytes - @ChangeInSize
WHERE EntityID = @ParentID AND MajorBuildNo = 0 AND MinorBuildNo = 0
SET @ChangeInSize = 0
END

/* For all other item of the same type as the new item's parent, perform the */
/* same update operation. */
IF (@ChangeInSize <> 0)
BEGIN

UPDATE BUILD SET BuildType = 0 WHERE MajorBuildNo = 0 AND MinorBuildNo = 0

DECLARE entDimCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT DISTINCT EntityID, TotalDimElements FROM ENTITY_ITEM
WHERE TypeID = @ParentID AND MajorBuildNo = 0 AND MinorBuildNo = 0

OPEN entDimCursor
DECLARE
@NextParentID int, @TotalDimElements int
FETCH NEXT FROM entDimCursor INTO @NextParentID, @TotalDimElements
WHILE @@fetch_status = 0
BEGIN
SET @ChangeInSize = @ChangeInSize * @TotalDimElements
EXEC UpdateSize @NextParentID, @ChangeInSize, 0

FETCH NEXT FROM entDimCursor INTO @NextParentID, @TotalDimElements
END
CLOSE entDimCursor
DEALLOCATE entDimCursor

END

go

Ex
Posting Yak Master

166 Posts

Posted - 2005-02-24 : 19:07:58

just trying to improve this design while keeping the structure well at least keeping the structure of CHANGEENTITYSIZE

was going to send in a table datatype into updatesize

also the updatesize proc is a child/parent recursion that i wont know the depth of that is really the thing slowing it all down at the moment

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-24 : 19:09:10

quote:

No, it is not supported.
A table variable only has scope within the code block where it is declared, and can't be passed into the scope of a stored procedure as an input parameter.
Use a temp table instead of the table variable.

BOL needs to be corrected then to say that all data types except tables can be used as input parameters.

Tara

PW
Yak Posting Veteran

95 Posts

Posted - 2005-02-24 : 19:19:37

BOL was corrected. See the Jan 2004 BOL update:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Under the Create Procedure topic:

"data_type

Is the parameter data type. All data types, except the table data type, can be used as a parameter for a stored procedure. [snip]"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-24 : 19:21:30

Now I could have sworn that I had that installed. This isn't the first time that I've come across this. Looking at my version, it no longer says service pack 3 in the title, which is what I think it's supposed to say if you have the update. I'll download again and install again. Thanks.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources