The way that works best for me, to write stored procedures that execute dynamic SQL, is by not using "EXEC sp_executesql" when I start writing the stored procedure, but by using "PRINT" statements.
I make sure I get to see each and every SQL that the stored procedure generates. I copy them from the Messages tab and execute them from within Management Studio. Only after all SQLs work as intended, I allow my stored procedure to "EXEC sp_executesql" (instead of simply PRINTing the SQL statements).
Debugging works very fast this way. The most problems I encountered so far, are the result of missing spaces, like 'FROMMyTableName' or 'WHEREId = 12'.
You will have to test this as the certificate user or with an account that has similar rights. And if you can avoid using dynamic SQLs, avoid them.
QUESTION #1
What is error in this procedure?
You seem to have some scope confusion on the variables
ANSWER TO QUESTION #1
PROBLEM : Your parameters have identical names to column names in the tables. This could produce some unpredictable results.
SOLUTION : Change the names of the parameters so that they are distinct from the column names
create procedure accountstatus
(
IN inmode varchar(27),
IN given_AccountStatus_id int,
IN given_AccountStatus varchar(255),
IN given_CreatedOn datetime,
IN given_CreatedBy varchar(255),
IN given_UpdatedOn datetime,
IN given_UpdatedBy varchar(255),
IN given_is_active bit)
Begin
if inmode = 'insert'
then
insert into accountstatus
(AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
values
(given_AccountStatus_id, given_Account_Status, given_CreatedOn, given_CreatedBy, given_UpdatedOn, given_UpdatedBy, given_is_active);
end if;
/*update*/
if inmode = 'update'
then
update accountstatus acc
set
-- acc.AccountStatus_id = given_AccountStatus_id, <- Not Needed for UPDATE
acc.Account_Status = given_Account_Status,
acc.CreatedOn = given_CreatedOn,
acc.CreatedBy = given_CreatedBy,
acc.UpdatedOn = given_UpdatedOn,
acc.UpdatedBy = given_UpdatedBy,
acc.is_active = given_is_active
where
acc.AccountStatus_id = given_AccountStatus_id;
end if;
/*delete*/
if inmode = 'delete'
then
update accountstatus acc
set
-- acc.AccountStatus_id = given_AccountStatus_id, <- Not Needed for DELETE
acc.is_active = 0
where
acc.AccountStatus_id = given_AccountStatus_id;
end if;
/*select*/
if inmode = 'select'
then
select * from accountstatus acc
where
acc.AccountStatus_id = given_AccountStatus_id;
end if;
end
CAVEAT : Please note that I commented out two lines
QUESTION #2
Is there any other ways to implement this procedure?
ANSWER TO QUESTION #2
You could use triggers
QUESTION #3
How it will effect the performance of the database?
ANSWER TO QUESTION #3
Doing bulk operations can make the MySQL server process to tedious work and bog it down. Here is other posts showing how to use SQL efficiently to replace a trigger and stored procedure, why too many triggers can be bad, and how as little code as possible
EPILOGUE
The simpler the code in the Stored Procedure or Trigger, the less impact on performance, especially on bulk INSERTs, UPDATEs, and DELETEs.
Please consider the Storage Engine and its locking characteristics (using MyISAM) when using triggers and the autocommit behavior (if using InnoDB).
Best Answer
When you create a new database in SQL Server the Model database is used as a template. Your new database will contain any stored procedurethat is in the model database.
From what you are describing it seems that the stored procedure, Searchinall is a custom procedure added to the model database by someone in your organization and then automatically included in any database that you created.