User defined data types and Stored procedures

[David Hrycyk]

I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message

Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3

Can you have user defined data types in stored procedures.

Store Procedure creation text

CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

Dave

[Larry]

It works fine for me (SQL2K). I just created a UDT called udt_test as varchar(30), and used it as 2 input parameters in a stored procedure and printed them out with no problems. Make sure your UDTs are in the same database as your stored procedures. Good luck.


------------
David Hrycyk at 3/13/01 4:54:12 AM

I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message

Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3

Can you have user defined data types in stored procedures.

Store Procedure creation text

CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

Dave

P.s. Make sure they're qualified by the username if appropriate, and that whoever executes the stored procedure has access to the UDT as well.


------------
Larry at 3/13/01 12:04:26 PM

It works fine for me (SQL2K). I just created a UDT called udt_test as varchar(30), and used it as 2 input parameters in a stored procedure and printed them out with no problems. Make sure your UDTs are in the same database as your stored procedures. Good luck.


------------
David Hrycyk at 3/13/01 4:54:12 AM

I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message

Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3

Can you have user defined data types in stored procedures.

Store Procedure creation text

CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

Dave

[David Hrycyk]

------------
at 3/13/01 12:08:59 PM

P.s. Make sure they're qualified by the username if appropriate, and that whoever executes the stored procedure has access to the UDT as well.


------------
Larry at 3/13/01 12:04:26 PM

It works fine for me (SQL2K). I just created a UDT called udt_test as varchar(30), and used it as 2 input parameters in a stored procedure and printed them out with no problems. Make sure your UDTs are in the same database as your stored procedures. Good luck.


------------
David Hrycyk at 3/13/01 4:54:12 AM

I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message

Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3

Can you have user defined data types in stored procedures.

Store Procedure creation text

CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

Larry
Thanks for the Info

The udt was defined with the prefix of dbo. I re-created the udt without the
dbo prefix and then recreated the procedure which worked successfully. I trhink I was under the impression that udts needed to be prefixed with the owner but this appears not to be the case.

Again thanks for your help

Dave

I recreated the user defined data type without this and recreated the stored procedure
without the prefix