Validating sql stored procedures
Validating user input in client code is important so that you do not waste round trips to the server.
On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values.
For more information about SQL injection and how to avoid it, see "SQL Injection" in SQL Server Books Online.
For more information about validating stored procedure parameters, see "Stored Procedures ( Database Engine)" and subordinate topics in SQL Server Books Online.
There are a few options that you could use for this.
The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters: ALTER PROCEDURE [dbo].[sp_Example] @parameter1 char (8), @parameter2 char (20), @parameter3 char (20), @parameter4 char (20), @parameter5 char (20), @parameter6 char (20),...
Rewrite as follows: alter procedure [email protected] int, @personid intasset nocount onif exists (select top 1 projectmanagerid from project where projectmanagerid = @personid)begin (select top (@count) count(pr.projectmanagerid) from person p inner join project pr on p.personid = pr.projectmanagerid )endelse print 'no record found' Hi, I am geeting an error msg 4104 (The multi-part identifier "pr.projectmanagerid" could not be bound.) Please help in resolving this error.