RE: Loading a variable

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Brad Feaker (BradFeaker_at_discussions.microsoft.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 11:25:04 -0800

declare @email_var varchar(8000)

select @email_var = select field1 + ' ' + field2 + ' ' + field3
from #temp27

This will work if there is only 1 row in #temp27.

If more than 1 row, write a cursor to scroll through #temp27 and combine all
the fields into a varchar variable
and execute the email send after exiting the cursor loop.

declare @email_body varchar(8000),
           @field1 varchar(50),
           @field2 varchar(50).
           @field3 varchar(50)

set @email_body = ''

declare email_cursor cursor local fast forward for
select * from #temp27

fetch next from email_cursor into @field1, @field2, @field3

while @@fetch_status <> -1

  set @email_body = @email_body + @field1 + @field2 + @field3

fetch next from email_cursor into @field1, @field2, @field3

email logic here

close email_cursor
deallocate email_cursor

Brad....

"Alley" wrote:

> Sorry I want the variable to be char or varchar so that I
> can email out the results using xp_sendmail.
>
>
> >-----Original Message-----
> >Try using a table variable...see the following article:
> >
> >http://www.aspfaq.com/show.asp?id=2475
> >
> >Brad Feaker
> >Ex nihilo, nihil fit
> >
> >"Alley" wrote:
> >
> >> Let's say I have a simple three column table, temp27,
> >> with a couple of rows in sql server 2000.
> >>
> >> How can I load "select * from temp27" into a
> variable. I
> >> am looking to do something like
> >>
> >> set @variablename = exec ('select * from temp27')
> >>
> >> but cannot get anything to work. Any tips would be
> >> appreciated.
> >>
> >.
> >
>

Relevant Pages

  • RE: Loading a variable
    ... write a cursor to scroll through ... #temp27 and combine all ... >and execute the email send after exiting the cursor loop. ...
    (microsoft.public.sqlserver.server)
  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: Optimize function that uses cursors
    ... > The function can be made recursive as there are no much recursions (we ... > groups and ancestor groups (i.e. the parents of the parents and so on) for ... > a recursive call in the select of the cursor), and in the cursor look it ... > declare @more bit ...
    (microsoft.public.sqlserver.programming)
  • Re: Replace Cursor Procedure with Update Query?
    ... DECLARE @tmpResults TABLE, Create_Date Int, Seq int) ... Can I replace the below cursor based procedure with an update> query? ... Every morning, after the update,> I run the procedure below to update the Sequence field. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Procedure Optimisation
    ... Any reason why you did not mention what DBMS product? ... I'm using a cursor to perform updates based on an ID. ... DECLARE @Site_ID NVARCHAR ... due to your EAV design of the source ...
    (comp.databases)