stored procedure @temp > 8000 characters

Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: stored procedure @temp > 8000 characters

  1. #1
    Join Date
    Dec 1969
    Posts
    1,388

    Default stored procedure @temp > 8000 characters

    How am i supposed to store a text string &#062; 8000 characters? I tried using text but SQL Server 7.0 did not like that.<BR><BR>Matt

  2. #2
    J.A.M Guest

    Default RE: stored procedure @temp > 8000 characters

    Is this in a variable in your stored procedure or actually in a table cell?<BR><BR>Have you considered the datatype nText?

  3. #3
    Join Date
    Dec 1969
    Posts
    1,388

    Default RE: stored procedure @temp > 8000 characters

    It is a varaible in my stored procedure that I will pass back to my asp program.<BR><BR>Matt

  4. #4
    Join Date
    Dec 1969
    Posts
    1,388

    Default RE: stored procedure @temp > 8000 characters

    I couldnt use nText it gave me the same error message saying that I could not use text,ntext or image data types as local variables.<BR><BR>Matt


  5. #5
    J.A.M Guest

    Default RE: stored procedure @temp > 8000 characters

    Yes, I&#039;ve had a similar &#039;problem&#039; this morning.I wanted to return a preformated html table from the procedure, rather than step through a recordset, but I didn&#039;t know how big the table can be at max - theoretically it could be larger than 8000 characters.<BR><BR>I&#039;m returning the recordset instead .... :/ <BR><BR>No, I&#039;m not overly happy with it, but whatcanyado?

  6. #6
    J.A.M Guest

    Default RE: stored procedure @temp > 8000 characters

    Have you tried TEXT?<BR><BR>My book here says that it can hold up to 2 gb of data ...

  7. #7
    Join Date
    Dec 1969
    Posts
    1,388

    Default RE: stored procedure @temp > 8000 characters

    Yeah but I CAN&#039;T use it as a variable in a stored procedure. Try creating a variable of type text and you will see what i mean.<BR><BR>Matt

  8. #8
    J.A.M Guest

    Default RE: stored procedure @temp > 8000 characters

    Like this?<BR><BR>@ioAction text OUTPUT, <BR><BR>I just copied that from a stored procedure and it wasn&#039;t kicking up a fuss .....

  9. #9
    Join Date
    Dec 1969
    Posts
    1,388

    Default RE: stored procedure @temp > 8000 characters

    Its giving me a lot of fuss. But i need to use a declare statement and the declare statement does not like adding the word output to it...maybe if you see my code you will see what exactly is going on. It looks like a mess when i copy and pasted it.<BR><BR>CREATE PROCEDURE sp_missinglogs <BR><BR> @firstdate datetime,<BR> @seconddate datetime,<BR><BR>AS<BR><BR>Declare @fax varchar(15), @termnum varchar(5),@termnum2 varchar(5),@flag int,@connum varchar(15),@contractorname varchar(50),@dateformonth varchar(15),@connumtemp varchar(15),@termnumtemp varchar(5),@temp varchar(8000)<BR><BR>set @temp=&#039;&#039;<BR><BR>DECLARE faxnumber Cursor for <BR><BR>SELECT login.dbo.agentsinfo.termnum,login.dbo.agentsinfo. fax from login.dbo.agentsinfo order by login.dbo.agentsinfo.termnum<BR><BR>open faxnumber<BR><BR> Fetch next from faxnumber<BR> into @termnum2,@fax<BR> <BR> While @@Fetch_Status = 0<BR> begin<BR><BR> set @flag=1<BR> <BR> Declare missinglogs Cursor for <BR> <BR> SELECT logs.contractornum,contractors.contractorname,cont ractors.termnum,date from logs INNER JOIN contractors on contractors.contractornum=logs.contractornum where date between &#039;7/1/01&#039; and &#039;7/5/01&#039; and logs=&#039;nologs&#039; order by contractors.termnum,logs.contractornum,date<BR><BR > open missinglogs<BR><BR> Fetch next from missinglogs<BR> into @connum,@contractorname,@termnum,@dateformonth<BR> <BR> While @@Fetch_Status = 0<BR> begin<BR> if (@connum&#060;&#062;@connumtemp AND @termnum2=@termnum)<BR> begin<BR><BR> if (@flag=1)<BR> begin<BR> set @temp=@temp + &#039;&#060;/div&#062;<BR><BR>&#060;div class=page&#062;&#060;table border=0 width=100%&#062;&#060;tr&#062;&#060;td&#062;&#060; img src=uslogo.gif&#062;<BR><BR>&#060;/td&#062;&#060;td valign=top&#062;&#060;u&#062;Managing Transportation Needs&#060;/u&#062;<BR>Phone: 800-245-4722 Fax:800-532-9275<BR>Phone: 412-264-6996 Fax: 412-264-1470<BR>&#060;/td&#062;&#060;/tr&#062;&#060;/td&#062;&#060;tr&#062;&#060;td width=50%&#062;Fax:&#039; + @fax + &#039;&#060;/td&#062;&#060;/tr&#062;&#060;tr&#062;&#060;td&#062;&#039; + @termnum2 + &#039;&#060;/td&#062;&#060;td&#062;&#060;/td&#062;&#060;/tr&#062;&#060;/table&#062;<BR><BR>The following is a month list of drivers who are behind in their logs. This list is faxed every week to your terminal. &#060;b&#062;We ask that at the time of dispatch you notify these drivers that they will not be paid this week if their name appears on this fax.&#060;/b&#062; The Department of Transportation allows 13 days for logs to be sent to the Corpate office. We will allow another 7 days only. If you know a log may be late, you must call Brenda at Ext 205 or Betty at Ext 208 and they will be glad to assist you. <BR><BR>Ronald E Steiner <BR> Director of Safety<BR><BR>&#039;<BR> set @flag=0<BR> end<BR> <BR> set @temp=@temp + &#039;<BR>&#039; + @contractorname + &#039;(&#039; + @connum + &#039;)(&#039; + @termnum + &#039;) - &#039; + @dateformonth + &#039; , &#039;<BR> end<BR> else<BR> if (@connum=@connumtemp and @termnum2=@termnum)<BR> begin<BR> set @temp=@temp + @dateformonth + &#039; , &#039;<BR> end<BR><BR> set @connumtemp=@connum<BR> set @termnumtemp=@termnum<BR><BR> Fetch next from missinglogs<BR> into @connum,@contractorname,@termnum,@dateformonth<BR> end <BR><BR> close missinglogs<BR> Deallocate missinglogs<BR> <BR> Fetch next from faxnumber<BR> into @termnum2,@fax<BR> END<BR><BR>CLOSE faxnumber<BR>Deallocate faxnumber<BR><BR>SELECT @temp

  10. #10
    Join Date
    Dec 1969
    Posts
    3,921

    Default God help you...

    Cursors and creating HTML from your stored procedures. What a performance and design nightmare. Enough of my complaining...<BR><BR>output parameters must be in the initial procedure declaration.<BR><BR>Here&#039;s a small SQL Server example that demonstrates this:<BR>http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=99

Posting Permissions

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