stored procedure help needed

Results 1 to 2 of 2

Thread: stored procedure help needed

  1. #1
    Join Date
    Dec 1969

    Default stored procedure help needed

    i think i have the problem narrowed down to the select statement in the sproc (below). basically if one of the 3 fields (state, league, month) requested doesn&#039;t contain a matching value in the recordset i get an exception occurred error. since i&#039;m using ANDs i&#039;m asking for recordsets that definitely contain results for 1,2, or all 3 of the fields. so if the state in the rs equals the request, but the month requested isnt in the rs, then i get the error. varLeague, varMonth, varState are all coming from drop down menus. so the user can select which fields they want to use to narrow down the search.<BR><BR>so how do i build the select to search only the fields requested, based on wether or not the variables contain info? instead of all 3? i can do it in a normal vb select, but not sure how to do it in a sproc. i even tried building it in the page, and sending it all to the sproc as a variable after the WHERE, but i just got errors.<BR><BR>also... how do i count the number of rs returned, and send that value to the page? would it look something like this?...<BR><BR>SELECT *,<BR>rscount = <BR>(<BR>SELECT COUNT(*) <BR>FROM #TempItems TI<BR>) <BR><BR>---------------------------------------------------------------------<BR>CREATE PROCEDURE sp_pbcalindex<BR>(<BR>@Page int,<BR>@RecsPerPage int,<BR>@varState varchar(75),<BR>@varLeague varchar(75),<BR>@varMonth int,<BR>@varSearch varchar(75)<BR>)<BR>AS<BR><BR>-- We don&#039;t want to return the # of rows inserted<BR>-- into our temporary table, so turn NOCOUNT ON<BR>SET NOCOUNT ON<BR><BR>--Create a temporary table<BR>CREATE TABLE #TempItems<BR>(<BR>tempID bigint IDENTITY,<BR>ID bigint,<BR>Title varchar(150),<BR>Description varchar(1500),<BR>State varchar(75),<BR>League varchar(75),<BR>StartDate datetime,<BR>EndDate datetime<BR>)<BR><BR>-- Insert the rows from tblItems into the temp. table<BR>INSERT INTO #TempItems (ID, Title, Description, StartDate, EndDate, State, League)<BR>SELECT ID, Title, Description, StartDate, EndDate, State, League FROM pbcal_events <BR>WHERE DatePart(m,StartDate) = @varMonth AND<BR>League LIKE &#039;%&#039; + @varLeague + &#039;%&#039; AND <BR>State LIKE &#039;%&#039; + @varState + &#039;%&#039; <BR>ORDER BY StartDate ASC <BR><BR>-- Find out the first and last record we want<BR>DECLARE @FirstRec int, @LastRec int<BR>SELECT @FirstRec = (@Page - 1) * @RecsPerPage<BR>SELECT @LastRec = (@Page * @RecsPerPage + 1)<BR><BR>-- Now, return the set of paged records, plus, an indiciation of we<BR>-- have more records or not!<BR>SELECT *,<BR>MoreRecords = <BR>(<BR>SELECT COUNT(*) <BR>FROM #TempItems TI<BR>WHERE TI.tempID &#062;= @LastRec<BR>) <BR>FROM #TempItems<BR>WHERE tempID &#062; @FirstRec AND tempID &#060; @LastRec<BR><BR>-- Turn NOCOUNT back OFF<BR>SET NOCOUNT OFF<BR>GO

  2. #2
    Join Date
    Dec 1969

    Default RE: stored procedure help needed

    I haven&#039;t fully read your post, but what I think you need is:<BR><BR>WHERE (League LIKE &#039;%&#039; + @varLeague + &#039;%&#039; OR &#039; + @varLeague + &#039;=&#039;&#039;) AND etc...<BR><BR>This will basically ignore the criteria if it&#039;s blank as you now have the &#039;&#039; equals &#039;&#039; clause.<BR><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