# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  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&#39;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&#39;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

----------

