Help with Stored Procedure

Results 1 to 2 of 2

Thread: Help with Stored Procedure

  1. #1
    Join Date
    Dec 1969

    Default Help with Stored Procedure

    Below I have pasted some code. I am passing two values from an asp page and am trying to return a recordset of information.<BR><BR>In this particular example I am setting up a temporary table to write data to for the first query and off of those results I am grabbing the data that I need.<BR><BR>Everything works perfectly fine if I hardcode the Location and the Property. When I pass my values I receive nothing back. Am I passing and grabbing the variables correctly?<BR><BR>When I call the procedure from the asp page I use the following call. <BR><BR>strSQL = "sp_Property_Management &#039" & strLocation & "&#039 , &#039" & strProperty & "&#039 "<BR> <BR>The stored procedure is as follows:<BR><BR>CREATE PROCEDURE sp_Property_Management<BR> (<BR> @Property varchar,<BR> @Location varchar<BR> )<BR>AS<BR><BR>--Create a temporary table<BR>CREATE TABLE #TempItems<BR>(<BR> FROMID [char](12),<BR> ADDRESSID [char](12),<BR>)<BR><BR><BR>-- Insert the rows into the temp table - first query<BR>INSERT INTO #TempItems (FROMID, ADDRESSID)<BR>SELECT sysdba.ASSOCIATION.FROMID, sysdba.ADDRESS.ADDRESSID FROM sysdba.[ACCOUNT] INNER JOIN sysdba.ASSOCIATION ON sysdba.ACCOUNT.ACCOUNTID = sysdba.ASSOCIATION.TOID <BR>INNER JOIN sysdba.ADDRESS ON sysdba.ACCOUNT.ADDRESSID = sysdba.ADDRESS.ADDRESSID<BR>WHERE (sysdba.ACCOUNT.TYPE = &#039BLDG OWNER&#039 AND sysdba.ACCOUNT.ACCOUNT = @Property)<BR><BR><BR>-- Now, return the set of records off of the temp table where the territory (location) matches<BR><BR><BR><BR>SELECT sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.STATUS, sysdba.ACCOUNT.TERRITORY<BR>FROM #TempItems INNER JOIN sysdba.ACCOUNT ON #TempItems.FROMID = sysdba.ACCOUNT.ACCOUNTID <BR>WHERE (sysdba.ACCOUNT.TERRITORY = @Location)<BR>ORDER BY sysdba.ACCOUNT.STATUS<BR><BR><BR><BR>Any help would be greatly appreciated. TIA<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Help with Stored Procedure

    try specifying the length of your varchars<BR><BR>CREATE PROCEDURE sp_Property_Management<BR>(<BR>@Property varchar(xx),<BR>@Location varchar(xx)<BR>

Posting Permissions

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