Avatar of RUA Volunteer2?
RUA Volunteer2?
Flag for United States of America asked on

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. 
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Horn

Yes.  Use them.

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. 
RUA Volunteer2?

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. 
SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck