?
Solved

Declaring @Local_Variable and @Parameters in Stored Procedure

Posted on 2013-01-08
7
Medium Priority
?
433 Views
Last Modified: 2013-01-09
Hi Experts,
Can we declare Local Variables and Parameters in a Stored Procedure in SQL?  I have couple of parameters to pass from the program, at the same time I need to declare couple of Local Variables to do some calaculations in the SP.  Please let me know.  Thank you very much in advance.
0
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Expert Comment

by:harshada_sonawane
ID: 38757819
yes
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 1000 total points
ID: 38757820
Yes, like this:

CREATE Procedure myProc (@Parm1 INT, @Parm2 VARCHAR(20))
AS
Begin
    Declare @Loc_Var int; -- etc
    --Your other code here using them something like this
    Set @Loc_Var = @Parm1;
End
0
 
LVL 12

Assisted Solution

by:Saurabh Bhadauria
Saurabh Bhadauria earned 1000 total points
ID: 38757884
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:RadhaKrishnaKiJaya
ID: 38760099
Thank you CGLuttrell.  I did exactly like that.  The problem is, I have 2 parameters passing from program.  Ovbiously I need 2 parameters in the SP.  Till this time I don't have any problem.  When I create the local variables (Declare @Loc_Var int; etc.) my C# program doesn't compile with the error message as below.  But when I remove ethe Declare statement from the SP, it compiles fine.  Any idea, how I can fix this issue?

DataLayer.DataModule' does not contain a definition for 'getXXXStoreProcedure' and no extension method 'getXXXStoreProcedure' accepting a first argument of type 'DataLayer.DataModule' could be found (are you missing a using directive or an assembly reference?)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38760331
Your original question has been fully answered, I would suggest if you need help with this followup question that you post the relevant code from your Stored Procedure and from C#.
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 38760415
acperkins, Thank you for your advice.
0
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 38760568
Thank you guys for your help.  I really appricate it.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question