Using A Stored Procedure instead of writing out SQL Statements
-
I'm really now to SQLServer. What I want to do is learn how to use stored procedures to accomplish what the SQL Statement below does. I've seen some examples of simpe Stored Procedures but non of them give me enough information to know see how to get this point. Can anyone help me?
strSQL = "SELECT CustomerName,YearsAD,Years,Sum(IIf([MonthNumber]=1,[ImageItems],0)) AS JanImage,Sum(IIf([MonthNumber]=1,[FullPay],0)) AS JanFulls, " _
& "Sum(IIf([MonthNumber]=1,[PartialPay],0)) AS JanPartial,Sum(IIf([MonthNumber]=1,[Multiples],0)) AS JanMulti,Sum(IIf([MonthNumber]=1,[ACT],0)) AS JanCkOnly, " _
& "Sum(IIf([MonthNumber]=1,[FLD],0)) AS JanCklist,Sum(IIf([MonthNumber]=1,[Lookups],0)) AS JanLookups,Sum(IIf([MonthNumber]=1,[Correspondence],0)) AS JanCorr, " _
& "Sum(IIf([MonthNumber]=1,[Unbankables],0)) AS JanUnb,Sum(IIf([MonthNumber]=1,[Express],0)) AS JanExp,Sum(IIf([MonthNumber]=1,[Photocopies],0)) AS JanSus, " _
& "Sum(IIf([MonthNumber]=1,[HSARCChecksConverted],0)) AS JanHSARC,Sum(IIf([MonthNumber]=1,[LSARCChecksConverted],0)) AS JanLSARC, " _
& "Sum(IIf([MonthNumber]=2,[ImageItems],0)) AS FebImage,Sum(IIf([MonthNumber]=2,[FullPay],0)) AS FebFulls " _
& "WHERE YearsAD = Years AND Years = '" & Request.Form("YearOne") & "' AND CustomerName = '" & Request.Form("Cid") & "' Group By CustomerName,YearsAD,Years"
-
CREATE PROC dbo.MonthReport @YearOne CHAR(4) , -- I an assuming that Year is a 4 character string such as 1999 @CID VARCHAR(50) -- I am assuming that CID is the customer name. AS SET NOCOUNT ON -- Gives a performance boost because SQL Server doesn't have to report the number of rows affected SELECT CustomerName, YearsAD, Years, Sum(CASE MonthNumber WHEN 1 THEN ImageItems ELSE 0 END) AS JanImage, Sum(CASE MonthNumber WHEN 1 THEN FullPay ELSE 0 END) AS JanFulls, Sum(CASE MonthNumber WHEN 1 THEN PartialPay ELSE 0 END) AS JannPartial, Sum(CASE MonthNumber WHEN 1 THEN Multiples ELSE 0 END) JanMulti, Sum(CASE MonthNumber WHEN 1 THEN ACT ELSE 0 END) JanCkOnly, Sum(CASE MonthNumber WHEN 1 THEN FLD ELSE 0 END) JanCklist, Sum(CASE MonthNumber WHEN 1 THEN Lookups ELSE 0 END) JanLookups, Sum(CASE MonthNumber WHEN 1 THEN Correspondence ELSE 0 END) JanCorr, Sum(CASE MonthNumber WHEN 1 THEN Unbankables ELSE 0 END) JanUnb, Sum(CASE MonthNumber WHEN 1 THEN Express ELSE 0 END) JanExp, Sum(CASE MonthNumber WHEN 1 THEN Photocopies ELSE 0 END) JanSus, Sum(CASE MonthNumber WHEN 1 THEN HSARCChecksConverted ELSE 0 END) JanHSARC, Sum(CASE MonthNumber WHEN 1 THEN LSARCChecksConverted ELSE 0 END) JanLSARC, Sum(CASE MonthNumber WHEN 2 THEN ImageItems ELSE 0 END) FebImage, Sum(CASE MonthNumber WHEN 2 FullPay ELSE 0 END) FebFulls FROM dbo.YourTable WHERE YearsAD = Years AND Years =@YearOne AND CustomerName = @CID Group By CustomerName,YearsAD,Years RETURN @@ROWCOUNT -- Not compulsory, I just like the RETURN_VALUE to be something useful. GO
Don't be afraid to space out your code to make it more readable.
I don't know how you are proposing to call the procedure but if you are proposing to use ADO or ADO.NET do some reading on the Command object.
Worst case scenario your query string above would be replaced by
EXEC dbo.MonthReport
-
Thank you very much for the reply!! Can I ask another question? You know how in access you can use the FORMAT feature in the query to change a date to a year or a month format. Like Format([VolDate],"yyyy"), "this is how I get the Year that I'm trying to include in the stored procedure". Can I include this in the same Stored Procedure so that I can create the year and the month? And if so, how what would it look like? I hope I'm making sense.
-
Firstly SQL Server has two functions that get the system date
GETDATE() retrieves the current system date
GETUTCDATE() which retrieves the Greenwich Meantime Date and Time
There are several ways of getting the YEAR or MONTH from a date
YEAR(GETDATE()), MONTH(GETDATE()) being one
DATEPART(year,GETDATE()), DATEPART(month,GETDATE()) being another.
You can also look at the CONVERT function
CONVERT(CHAR(4),GETDATE(),112) converts the date to YYYYMMDD but as the output is only four characters long you effectively get the YEAR.
If whatever your programming language is does not support a specific datetime datatype then your best bet is to pass the date into the procedure as a string in a format dictated by yourself and then use convert to meld it into a date.
For example, if you pass in a date in the format YYYYMMDD then you can use CONVERT(SMALLDATETIME,@YourDateParam,112) to convert the input into a date.
Do you have SQL Server Books on Line? The link is to the last update for SQL 2000 BOL.
Read up on CONVERT and DATEPART in there
-
Sorry about not responding sooner. I was away from my PC over the weekend. But thanks for the response you sent. It really proved helpfule. Using what you mentioned I'm trying to create a simple login script that will allow me to validate a user against what is in the database but I keep getting this error. The line 29 that has the error is in Red in the code below. What am I doing wrong?
Error Type:
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/Password.asp, line 29
Login form:
<div align="center">
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="12%">
<tr>
<td>
<form method="POST" Action="Password.asp">
<p align="center"><input name="UserName" size="20" style="float: left"></p>
</td>
</tr>
<tr>
<td> <p align="center">
<input name="Password" size="20" style="float: left"></p></td>
</tr>
<tr>
<td> <input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
</form>
</div>
Stored Procedure:
CREATE PROCEDURE [dbo].[sp_IsValidLogon]
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From StarAdmin
Where Supervisors = @UserName
And
Pass = @Password)
return(1)
else
return(0)
GO
Calling Stored Procedure in ASP
<%
<!--#INCLUDE VIRTUAL="/include/adovbs.inc"-->
dim dataConn, adocmd, IsValid
Dim myConn,strConnection
Set myConn = Server.CreateObject("ADODB.Connection")
'Connect to SQL Server Northwind
strConnection = "PROVIDER=SQLOLEDB; DATA SOURCE=REMITCOCLTVOL;" _
& "database=REMITCOSQLServer;Trusted_Connection=yes;"
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.Open strConnection
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "sp_IsValidLogon"
adocmd.ActiveConnection = myConn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter("return", _
adInteger, adParamReturnValue, 4)
adocmd.Parameters.Append adocmd.CreateParameter("UserName", _
adVarChar, adParamInput, 16, _
Request.Form("UserName"))
adocmd.Parameters.Append adocmd.CreateParameter("Password", _
adVarChar, adParamInput, 16, _
Request.Form("Password"))
adocmd.Execute
IsValid = adocmd.Parameters("return").Value
If IsValid = 0 Then
Response.Redirect "Incorrect.htm"
Else
Response.Redirect "correct.htm"
End If
'process logon code
'.............
%>
-
What value do you have for adCmdStoredProc?
-
Okay, this is where I get confused. I thought that the parameters @UserName and @Password were all I needed. What value would I need to pass to adocmd.CommandType = adCmdStoredProc? Would it be the auto number of the record which is called "Datatable"? I hope you can be patient with me.
-
The value of 'adCmdStoredProc' should be defined in the file 'adovbs.inc' and the declaration should be: Const adCmdStoredProc = &H0004. It tells ADO what type of statement is being executed, in this case a stored procedure.
--Jeff
-
JeffB, and all the rest. Thanks for the help you all provided me in my efforts to understand using stored procedures. It's very much appreciated!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply