April 29, 2015 at 2:57 pm
I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.
USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
( @id INTEGER,
@firstname VARCHAR(10) = null,
@lastname VARCHAR(10) = null,
@salary DECIMAL(10,2) = null,
@city VARCHAR(20)= null,
@StatementType nvarchar(20) = '')
AS
BEGIN
IF @StatementType = 'Insert'
BEGIN
insert into employee (id,firstname,lastname,salary,city) values( @id, @firstname, @lastname, @salary, @city)
END
IF @StatementType = 'Select'
BEGIN
select * from employee
END
IF @StatementType = 'Update'
BEGIN
UPDATE employee SET Firstname = @firstname, lastname = @lastname, salary = @salary, city = @city WHERE id = @id
END
else IF @StatementType = 'Delete'
BEGIN
DELETE FROM employee WHERE id = @id
END
end
So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?
April 29, 2015 at 3:06 pm
April 29, 2015 at 3:12 pm
I would also add validation to your procedure. For example, what if you run this:
EXEC MasterInsertUpdateDelete @firstname = 'John', @lastname = 'Smith', @city = 'Denver', @StatementType = 'Delete'
Your Delete statement only accepts @id which isn't supplied. Your procedure should immediately check the @StatementType and ensure that the appropriate parameters have been supplied.
-SQLBill
April 29, 2015 at 3:20 pm
SQLBill (4/29/2015)
I would also add validation to your procedure. For example, what if you run this:EXEC MasterInsertUpdateDelete @firstname = 'John', @lastname = 'Smith', @city = 'Denver', @StatementType = 'Delete'
Your Delete statement only accepts @id which isn't supplied. Your procedure should immediately check the @StatementType and ensure that the appropriate parameters have been supplied.
-SQLBill
the @id is not nullible so there will be an error 201 thrown if not provided.
April 29, 2015 at 4:46 pm
Not, in general, a good idea. Procedures with multiple branching logic often suffer from performance problems, and besides it's against good software engineering practices (single responsibility principle)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2015 at 5:18 pm
I believe SQL will compile all statements when the proc is loaded, so there's overhead there.
If you do this, though, at least make it as efficient as possible by using the "ELSE IF" approach throughout the code.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
( @id INTEGER,
@firstname VARCHAR(10) = null,
@lastname VARCHAR(10) = null,
@salary DECIMAL(10,2) = null,
@city VARCHAR(20)= null,
@StatementType nvarchar(20) = ''
)
AS
SET NOCOUNT ON;
IF @StatementType = 'Select'
BEGIN
select * from employee
END
ELSE IF @StatementType = 'Update'
BEGIN
UPDATE employee
SET Firstname = @firstname, lastname = @lastname, salary = @salary, city = @city
WHERE id = @id
END
ELSE IF @StatementType = 'Insert'
BEGIN
insert into employee (id,firstname,lastname,salary,city)
select @id, @firstname, @lastname, @salary, @city
END
ELSE IF @StatementType = 'Delete'
BEGIN
DELETE FROM employee
WHERE id = @id
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 29, 2015 at 5:56 pm
I would be wary of a design like that. How would you deny update/delete on a table if you had all of your stored procedures like that? If they only did one thing, you'd just deny to the stored procedures that did update and delete. Problem solved. With your solution, it would get complicated fast.
April 29, 2015 at 6:58 pm
There are pros and cons to this but here's an article with some templates:
Using Stored Procedures to Provide an Application’s Business-Logic Layer [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 29, 2015 at 7:20 pm
Thanks everyone for the responses. I can see that although it is possible I think I should separate the statements for simplicity and speed. I appreciate how everyone came together to help me with this!. Thank you
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply