Hi All,

       I have developed a module which has lot of search functions in it.For getting the search functionality workking i have written the stored procedure for example in the following way.

ex :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure dbo.XWD_CompetencyManager_Catogery_Search
@CatogerySearch nvarchar(50)

as

select CatogeryId,
          CreatedByUser,
          CreatedDate,
          CatogeryName,
          CatogeryDesc,
          ParentCatogeryId,
          SkillLevelTypeId

from  XWD_CompetencyManager_Catogery
where  CatogeryName like '%' + @CatogerySearch + '%'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The above procedure when added to a sqldataprovider in the module package is throwing an error as below.

SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near '@CatogerySearch'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE procedure dbo.XWD_CompetencyManager_Catogery_Search @CatogerySearch nvarchar(50) as select CatogeryId, CreatedByUser, CreatedDate, CatogeryName, CatogeryDesc, ParentCatogeryId, SkillLevelTypeId from XWD_CompetencyManager_Catogery where CatogeryName like '%' @CatogerySearch '%' System.Data.SqlClient.SqlException:

But when the same procedure is run in sql under host that is host-->sql it is running fine.But when i make a module pack and save this prcedure in the sqldataprovider file it is throwing an above error .

The same is being repeated with all the search procdures.Please help its urgent.

Thanks,

Sandeep.M