Declaring Parameters in Microsoft SQL Server. Rules Best practices placement etc.
Just trying to figure out what I frequently do wrong with declaring variables. Are there any rules to them like where they should always be placed or any best practices to ensure they work.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I seem to get errors a lot around this issue. I could type them all out. My question is simple when declaring parameter values in an SP does it always appear above the select statement. So if I had the following
Is it
DECLARE
AS
@prodtype
@startdate
@enddate
@opencanc
BEGIN
or
AS
DECLARE @prodtype
DECLARE @startdate
DECLARE @enddate
DECLARE @opencanc
BEGIN
Select * From SomeTable
I constantly get errors around setting this up to test an SP very frustrating and I thought maybe there were some rules guidelines best practices to set them up for success.
Is it
DECLARE
AS
@prodtype
@startdate
@enddate
@opencanc
BEGIN
or
AS
DECLARE @prodtype
DECLARE @startdate
DECLARE @enddate
DECLARE @opencanc
BEGIN
Select * From SomeTable
I constantly get errors around setting this up to test an SP very frustrating and I thought maybe there were some rules guidelines best practices to set them up for success.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Beyond that I'm really not clear on what your question is, as how parameters are placed within a CREATE PROC declaration has no impact on the execution plan(s) generated.
As far as declaring variables within a SP, same thing, where they are at has no bearing on the execution plan, so I would consider that more overall cosmetics of making the SP easy to read as opposed to a spaghetti pile of pre-Egyptian hieroglyphics-looking code that is impossible to support.