CODEWITHSUNDEEP
c#asp.nett-sqlstored-procedures
Kpt.Khaos
Kpt.Khaos

Reputation: 693

Calling a stored procedure without any parameteres c#

I'm new to stored procedures in T-SQL and I am trying to call my stored procedure to work on my asp page. I am not sure how to go about this do I just create a SQL String for my Select statement or do I have to include all the declare variables. I have posted my declare and select statement below. Thank you for your help.

DECLARE @CasesPerHour decimal(18,2);

Declarations
DECLARE @Line1Start datetime;
DECLARE @Line1Breaks decimal(10,2);
DECLARE @Line1ScaleCount int;
DECLARE @Line1Scale1Percentage varchar(10);
DECLARE @Line1Scale2Percentage varchar(10);
DECLARE @Line1Scale3Percentage varchar(10);
DECLARE @Line1Scale4Percentage varchar(10);
DECLARE @Line1Scale5Percentage varchar(10);
DECLARE @Line1Scale6Percentage varchar(10);
DECLARE @Line1Scale7Percentage varchar(10);
DECLARE @Line1Percentage  varchar(10);
DECLARE @Line1TargetCaseCount decimal(10,2);


Set statement

SET @CasesPerHour = 70.00

--Line 1 Sets
SET @Line1Start = (SELECT TOP 1 PrintDateTime
FROM CompletedCaseManifest
WHERE LineNumber = 1 and PrintDateTime > (CASE WHEN (cast(getdate() as time(0)) < '06:00:00' OR cast(getdate() as time(0)) > '17:15:00') then (CASE WHEN cast(getdate() as time(0)) < '06:00:00' then DATEADD(day, DATEDIFF(day, 1, GETDATE()), '17:15:00') else DATEADD(day, DATEDIFF(day, 0, GETDATE()), '17:15:00') end) else DATEADD(day, DATEDIFF(day, 0, GETDATE()), '06:00:00') end))
SET @Line1Breaks = ISNULL((SELECT SUM(BreakLength) FROM BreakTracker WHERE LineNumber = 1 AND BreakStartTime > @Line1Start), 0)/60.00
SET @Line1ScaleCount = (SELECT COUNT(Distinct ScaleNumber) FROM CompletedCaseManifest Where LineNumber = 1 and PrintDateTime > @Line1Start)
SET @Line1TargetCaseCount = (((datepart(minute,convert(varchar(8),cast(getdate() - @Line1Start as time(0))))/60.00)
 + datepart(hour,convert(varchar(8),cast(getdate() - @Line1Start as time(0)))) - @Line1Breaks)*@CasesPerHour)
SET @Line1Scale1Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and ScaleNumber = 1 and CaseVoided = 0 and PrintDateTime > @Line1Start)/@Line1TargetCaseCount)*100)
SET @Line1Scale2Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and ScaleNumber = 2 and CaseVoided = 0 and PrintDateTime > @Line1Start)/@Line1TargetCaseCount)*100)
SET @Line1Scale3Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and ScaleNumber = 3 and CaseVoided = 0 and PrintDateTime > @Line1Start)/@Line1TargetCaseCount)*100)
SET @Line1Scale4Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and ScaleNumber = 4 and CaseVoided = 0 and PrintDateTime > @Line1Start)/@Line1TargetCaseCount)*100)
SET @Line1Scale5Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and ScaleNumber = 5 and CaseVoided = 0 and PrintDateTime > @Line1Start)/@Line1TargetCaseCount)*100)
SET @Line1Scale6Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and ScaleNumber = 6 and CaseVoided = 0 and PrintDateTime > @Line1Start)/@Line1TargetCaseCount)*100)
SET @Line1Scale7Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and ScaleNumber = 7 and CaseVoided = 0 and PrintDateTime > @Line1Start)/@Line1TargetCaseCount)*100)
SET @Line1Percentage = Convert(decimal(10,2),((SELECT COUNT(CaseID) FROM CompletedCaseManifest WHERE LineNumber = 1 and CaseVoided = 0 and PrintDateTime > @Line1Start)/(@Line1TargetCaseCount*@Line1ScaleCount))*100)

Select Statement

Select @Line1Scale1Percentage + '%' as Line1Scale1, 
@Line1Scale2Percentage + '%' as Line1Scale2, 
@Line1Scale3Percentage + '%' as Line1Scale3, 
@Line1Scale4Percentage + '%' as Line1Scale4, 
@Line1Scale5Percentage + '%' as Line1Scale5, 
@Line1Scale6Percentage + '%' as Line1Scale6, 
@Line1Scale7Percentage + '%' as Line1Scale7, 
@Line1Percentage + '%' as Line1Average,

Upvotes: 0

Views: 205

Answers (2)

Leonard Wilson
Leonard Wilson

Reputation: 82

A stored procedure is called with the EXEC statement (http://technet.microsoft.com/en-us/library/ms189915.aspx).

A variable declared within the body of the stored procedure does not get passed to it. Only the parameters, specified in parentheses before the body of a stored procedure, get passed to it from outside.

The Microsoft documentation on stored procedures (http://technet.microsoft.com/en-us/library/ms187926.aspx) can get eye-glazing, but may clear up for you if you focus on looking at the examples toward the bottom.

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Generally, calling a stored procedure is just executing an SqlCommand with the StoredProcedure command type:

using (SqlCommand cmd = new SqlCommand("storedProcedureName", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
}

Whether you use ExecuteNonQuery or any other of the ExecuteXYZ methods depends on whether you expect results from the call or not. If the stored procedure returns a result set, you may, for example, want to use ExecuteReader like this:

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        // Handle one item of the result set.
    }
}

In your case, the stored procedure returns one record, so you may want to use ExecuteReader.

Upvotes: 1

Related Questions