Learning Stored Procedures

Results 1 to 3 of 3

Thread: Learning Stored Procedures

  1. #1
    Murf Guest

    Default Learning Stored Procedures

    I am attempting to learn how to execute a stored procedure from asp. I am trying to take baby steps in order to get a great distance in a short period.<BR><BR>So here goes. I have written a stored procedure here is the code:<BR><BR>CREATE PROCEDURE zipcode_lookup (@zipcode varchar(15)) as<BR><BR>DECLARE @rc Int<BR><BR>SELECT zipcode, longitude, latitude <BR> FROM zipcodelist <BR> WHERE zipcode = @zipcode<BR><BR>IF @@RowCount &#062; 0<BR> Begin<BR> SELECT @rc = 0<BR> RETURN @rc<BR> END<BR><BR>Else<BR> Begin<BR> SELECT @rc = 1<BR> Return @rc<BR> END<BR><BR>This executed completely and now under stored procedures for this database I have a new stored procedure called:<BR><BR>zipcode_lookup<BR><BR>What I am wanting to do is pass a zipcode to the stored procedure, and returning a value (@rc) of 0 if it exists or a value of 1 if it does not.<BR><BR>Here is my simple code for the asp page:<BR><BR>&#060;% <BR><BR>dim oConn<BR><BR>set oConn = Server.createobject("ADODB.Connection")<BR>oConn.O pen "Provider=SQLOLEDB.1;DSN=cyber;UID=11111;PWD=11111 "<BR>oConn.execute "zipcode_lookup 64015"<BR><BR><BR>oConn.Close<BR>set oConn = Nothing<BR><BR> %&#062;<BR><BR>I don&#039t get any errors but I know my execute statement is not correct for what I am wanting.<BR><BR>My question is, how do I pass a value to the stored procedure and how do I retrieve the @rc value.<BR><BR>Thanks

  2. #2
    RipCat Guest

    Default I think this will do it

    In answer to first question...Set a string variable to contain your string to call sp...<BR><BR>Here&#039s the asp Code:<BR><BR> lsCustomerMasterMaintenanceSql = "up_CustomerMasterSelect " & lcCustomerMasterId<BR><BR>where lcCustomerMasterId is the parameter to be passed...Make sure space after sp name and parameter...<BR><BR> Set orsCustomerMasterMaintenance = ocnCustomerMasterMaintenance.Execute(lsCustomerMas terMaintenanceSql)<BR><BR>I typically have my stored procedure already created...<BR><BR>Here&#039s stored procedure...<BR><BR>Alter Procedure up_CustomerMasterSelect<BR><BR> @Parameter Int<BR><BR>As<BR><BR> SELECT FieldList<BR> FROM TableName<BR> WHERE TableName.FieldName = @Parameter<BR><BR>Return<BR><BR>This will solve first problem...How to call with parms...<BR><BR>Now for the second part...It&#039s a little different from the above...It&#039s your final solution...<BR><BR>You have to create a command object and then add the parameters...I usually add my return value parameter first...<BR><BR> lcCustomerMasterId = ""<BR> <BR> Set ocmCustomerMasterSave = Server.CreateObject("ADODB.Command")<BR><BR> ocnCustomerMasterSave.Open lsCustomerMasterSaveConn<BR> <BR> ocmCustomerMasterSave.ActiveConnection = ocnCustomerMasterSave<BR> <BR> ocmCustomerMasterSave.CommandType = 4<BR><BR> ocmCustomerMasterSave.CommandText = "up_CustomerMasterInsert " <BR> <BR>Make sure space after sp name...<BR><BR> ocmCustomerMasterSave.Parameters.Append _<BR> ocmCustomerMasterSave.CreateParameter("ReturnValue ", adInteger, adParamReturnValue, 4)<BR><BR>You will have appends for each parameter passed to sp...<BR><BR> ocmCustomerMasterSave.Parameters.Append _<BR> ocmCustomerMasterSave.CreateParameter("Parm1", _<BR> adInteger, adParamInput, 4, Request.Form("Parm1"))<BR><BR> ocmCustomerMasterSave.Execute<BR><BR> lcCustomerMasterId = ocmCustomerMasterSave.Parameters("ReturnValue").va lue<BR><BR>That&#039s the only two you&#039ll need for your example...A few things to remember...Here&#039s the constants you&#039ll need from adoVbs.INC<BR><BR>Const adParamInput = &H0001<BR>Const adParamReturnValue = &H0004<BR>Const adInteger = 3<BR>Const adChar = 129<BR> <BR>Be sure to remember what type of parameter you want to pass on each append and the data type of the passed parameter...It should be the same in the parameter.append and the stored procedure declaration and the underlying table field def...<BR><BR>Here&#039s an example insert..The first field inserted (not listed in parm list) is the auto number unique id and is what is returned...The other are just params<BR><BR>Alter Procedure up_CustomerMasterInsert<BR><BR> @Name Char(75), <BR> @CustomerStatus Char(1), <BR> @Description Char(75),<BR> @eMail1 Char(35), <BR> @eMail2 Char(35), <BR> @Phone1 Char(13), <BR> @Phone2 Char(13), <BR> @Fax1 Char(13),<BR> @Fax2 Char(13), <BR> @ContactName Char(50), <BR> @ContactPhone Char(13)<BR> <BR>As<BR><BR> INSERT CustomerMaster<BR> (<BR> Name, CustomerStatus, Description, eMail1, eMail2, Phone1, Phone2, Fax1, Fax2, <BR> ContactName, ContactPhone<BR> )<BR> VALUES<BR> (<BR> @Name, @CustomerStatus, @Description, @eMail1, @eMail2, @Phone1, @Phone2, @Fax1, @Fax2, <BR> @ContactName, @ContactPhone<BR> )<BR><BR>Return @@IDENTITY <BR><BR>The above statement puts whatever you want to return into the return parameter...I&#039ve only used return values for inserts but it will work with any stored procedure...<BR><BR>ReturnVariable = ocmCustomerMasterSave.Parameters("ReturnValue").va lue<BR><BR>This is kinda a long winded explanation but reasonably straight forward to do...You might have to struggle a little...But...You&#039ll get it...<BR><BR>Hope this helps...<BR><BR>RipCat<BR><BR>

  3. #3
    Murf Guest

    Default RE: I think this will do it

    Thanks for the help, I did get it to work with little difficulty. Now on to more complex parts of the project...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts