Stored Procedure that returns a HTML DROPDOWN LIST

Results 1 to 3 of 3

Thread: Stored Procedure that returns a HTML DROPDOWN LIST

  1. #1
    Kate Guest

    Default Stored Procedure that returns a HTML DROPDOWN LIST

    I have set up a stored procedure which returns a HTML drop down Option list. However I have discovered that only the first 30 items in the list are been displayed. I need it to display all remaining items (57 in total).<BR><BR>I have copied my stored proc. here. I would be really grateful if someone could see why it might only be pulling the first 30 or if I need to change some sort of default in SQL !<BR><BR>Thanks for your time...<BR><BR><BR>--STORED PROCEDURE<BR><BR>CREATE PROCEDURE spGetUserDetailsForDept<BR> @DeptID int,<BR> @ListName varchar(10)<BR>AS<BR><BR>SET NOCOUNT ON<BR><BR>DECLARE @UserName varchar(50), @FirstName varchar(50), @SurName varchar(50),@List varchar(2000), @TeamName varchar(50)<BR><BR>select @List = "&#060;select size=""1"" name=""" + @ListName + """ style=""font-family: Arial Narrow; font-size: 10pt; WIDTH: 200px""&#062;" + CHAR(13) <BR>select @List = @List + "&#060;option value="""" selected&#062;Select a User&#060;/option&#062;" +CHAR(13)<BR><BR>DECLARE UserTeamDetails_Cursor CURSOR FOR <BR><BR>SELECT<BR> a.UserName,a.FirstName, a.Surname,<BR>(select b.GroupDescription from Groups b<BR> where a.Team = b.GroupID) as TeamName<BR>FROM<BR> Users a<BR>WHERE<BR> a.Department = @DeptID<BR>ORDER BY<BR> a.Surname asc<BR><BR><BR>Open UserTeamDetails_Cursor<BR><BR>FETCH NEXT FROM UserTeamDetails_Cursor <BR><BR>INTO @UserName,@FirstName, @Surname,@TeamName<BR><BR>WHILE @@FETCH_STATUS = 0 <BR><BR>BEGIN<BR> <BR> Select @List = @List + "&#060;option value=""" + @UserName + """&#062;" + @SurName + "," + @FirstName + " - " + @TeamName + "&#060;/option&#062;" +CHAR(13)<BR> <BR> FETCH NEXT FROM UserTeamDetails_Cursor <BR> INTO @UserName,@FirstName, @Surname,@TeamName<BR>END<BR><BR>CLOSE UserTeamDetails_Cursor<BR><BR>DEALLOCATE UserTeamDetails_Cursor<BR><BR>Select @List = @List + "&#060;/select&#062;"<BR>Select @List<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Stored Procedure that returns a HTML DROPDOWN

    I put your code into sql query analyser and found that each list item was about 100 characters long. That being the case, it wouldn&#039;t take it long to get up to the 2000 character length that you&#039;ve set @list to. Make it a varchar(8000) - the longest a varchar can be - and that should make it come out properly

  3. #3
    Kate Guest

    Default Brilliant ! That worked a treat...

    Thanks for your help...

Posting Permissions

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