Query

Sep 10, 2013 at 7:12 PM
Hello to all ,

I'm starting to develop asp.net and I am facing a problem related to query the database .

This giving the following error :

Msg 2715, Level 16 , State 3 , Procedure InsertItem , Line 1
Column , parameter , or variable # 8 : Can not find data type CustomFieldValuesType .

The error is happening when I crirar this procedure :

CREATE PROCEDURE dbo.InsertItem
@ CategoryId integer ,
@ EmailAddress national character varying ( 128 ) ,
@ AddedBy national character varying ( 32 ) ,
@ Title national character varying ( 32 ) ,
@ Price money ,
@ PriceType smallint ,
@ Details national character varying ( 4000 ) ,
@ CustomFieldsTable CustomFieldValuesType READONLY ,
@ OtherFieldsTable OtherFieldsType READONLY ,
@ ItemId bigint OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ Timestamp datetime = GETDATE ( ) ;
DECLARE @ ErrorMessage national character varying ( 4000 ) ;
DECLARE @ ErrorSeverity integer;
DECLARE @ ErrorState integer;

Tinyint DECLARE @ RetVal = 1 ;

BEGIN TRY
BEGIN TRANSACTION;

INSERT INTO dbo.Items ( CategoryId , EmailAddress , AddedBy , Title , Alias ​​, Price, PriceType , Details , AddedOn , LastEditedOn , TotalEdits , ExpiresOn , LastViewedOn , TotalViews )
SELECT @ CategoryId , @ EmailAddress , @ AddedBy , @ Title , dbo.GetAlias ​​( @ Title) , @ Price, @ PriceType , @ Details , GETUTCDATE ( ) , GETUTCDATE ( ) , 0 , GETUTCDATE ( ) + 60 , GETUTCDATE ( ) 1;

SET @ ItemId = SCOPE_IDENTITY ( ) ;

INSERT INTO dbo.ItemCustomFields ( ItemId , CustomFieldId , FieldValue )
SELECT @ ItemId , CustomFieldId , FieldValue
FROM @ CustomFieldsTable ;

INSERT INTO dbo.ItemOtherFields ( ItemId , FieldName , FieldValue )
SELECT @ ItemId , FieldName , FieldValue
FROM @ OtherFieldsTable ;

IF @ @ TRANCOUNT > 0 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SET @ RetVal = 0 ;
IF @ @ TRANCOUNT > 0 ROLLBACK TRANSACTION;

SELECT @ ErrorMessage = ERROR_MESSAGE ( ) ;
SELECT @ ErrorSeverity ERROR_SEVERITY = ( ) ;
SELECT @ ErrorState ERROR_STATE = ( ) ;
RAISERROR ( @ ErrorMessage , @ ErrorSeverity , @ ErrorState ) ;
END CATCH


RETURN @ RetVal ;
END;

GO

Any help is very welcome! !
Coordinator
Sep 10, 2013 at 8:52 PM
I am not exactly sure what you are trying to do. The "CustomFieldValuesType" is user defined table type as defined in the script:

https://mixnp.codeplex.com/SourceControl/latest#sql-script/mixnp-db.sql

What version of SQL Server are you using?
Sep 10, 2013 at 10:06 PM
So I'm using sql server 2008 r2.

Even the newest query continues giving error

this part of the query

@ CustomFieldsTable CustomFieldValuesType READONLY,
@ OtherFieldsTable OtherFieldsType READONLY,

It is not recognizing the CustomFieldValuesType as a data type
and also the OtherFieldsType.

Thank you!
Coordinator
Sep 11, 2013 at 6:34 AM
Read my previous comment once again. Sorry, you are missing it completely. You have to understand the concepts first. @CustomFieldsTable and @OtherFieldsTable are UDTTs. Go through the following article about User-Defined Table Types:

http://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

Moreover, you have to run the whole query instead of the just the procedure. You cannot just copy and paste this stored procedure and compile.