1. 

   pand052, Member

   I am using a MS SQL database and I am on a premium license of MYDBR 4.3.1 build 2525
   I have been using this product for about 6 months now to successfully run basic select statements but I wanted to try adding some search functionality by using parameters but I've been banging my head against the wall for the past two hours and just can not figure it out.

   I can create a SP and it works just fine but as soon as I try to add a parameter to it I get an incorrect syntax message.

   Here's the code that works:

   CREATE PROCEDURE sp_DBR_testparameters
   AS
   BEGIN

   SELECT *
   FROM mytable

   END
   GO

   I am simply trying to reproduce the example in the documentation at this point so I modify it to be:

   CREATE PROCEDURE sp_DBR_testparameters( vFilmName nvarchar(30) )
   AS
   BEGIN

   SELECT *
   FROM mytable
   where notes like concat('%',vFilmName,'%');

   END
   GO

   but when I execute this it says:

   message: Incorrect syntax near 'vFilmName'.
   Your procedure has not been saved to database.
   Fix the error or revert back to original.

   even when I comment out the WHERE clause I still get the error message which tells me that I am probably either defining the parameter incorrectly or possibly have some settings wrong in the application?

   Any ideas on what I am doing wrong?

   Thanks!
2. 

   myDBR Team, Key Master

   You seem to be using mixed MySQL and SQL Server syntax. In SQL Server parameters (and variables) have @-prefix. Also, CONCAT is not supported in all SQL Server versions, you can just use '+'-operator for string concatenation. Try following:

   CREATE PROCEDURE sp_DBR_testparameters( @vFilmName nvarchar(30) )
   
   AS
   
   BEGIN
   
   SELECT *
   
   FROM mytable
   
   where notes like '%'+@vFilmName+'%'
   
   END
   
   GO

   --
   myDBR Team
3. 

   pand052, Member

   wow, I figured I was probably doing something minor incorrectly. That fixed my issue, thanks!

Reply

You must log in to post.