Need to assign value to declared variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to assign value to declared variable

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-10 : 15:30:45

I have a sp and I need to return the result from the select in a 500 character long variable (ie assign value of the JobnoteEvent.Note to @JobNotes). I tried doing this:

select jobnoteevent.note as @JobNotes

and gives my syntax error.

CREATE PROCEDURE sp_Vecellio_GetProductionNotes  

@JobNumber int,
@ItemNumber char(20),
@NoteDate datetime


as

declare @JobNotes char(500)

select   jobnoteevent.note as @JobNotes 
from dbo.jobnoteevent
	inner join event on event.eventguid = jobnoteevent.eventguid
	inner join batch on batch.batchguid = event.batchguid
	inner join job on job.jobguid = event.jobguid
	inner join item on item.itemguid = event.itemguid
where job.companyjobid = @JobNumber and item.companyitemid = @ItemNumber and batch.reportdate = @NoteDate
GO

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-07-10 : 15:52:57

select @JobNotes = jobnoteevent.note from ...

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-10 : 16:02:43

Yes, I tried this as well and syntax is OK. Now the data raeader in my vb program does not see anything being returned? Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-10 : 16:06:10

The variable is internal to the SP.

If you want to return the Note column, why pushing it to a variable in the first place?

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-10 : 16:19:53

The Note column is a 500 nvarchar and I always need a fixed 500 character length returned.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-10 : 16:24:10

[code]as

set @NoteDate = datediff(day, '19000101', @notedate)

select cast(jobnoteevent.note as char(500))
from dbo.jobnoteevent
inner join event on event.eventguid = jobnoteevent.eventguid
inner join batch on batch.batchguid = event.batchguid
inner join job on job.jobguid = event.jobguid
inner join item on item.itemguid = event.itemguid
where job.companyjobid = @JobNumber
and item.companyitemid = @ItemNumber
and batch.reportdate >= @NoteDate
and batch.reportdate < dateadd(day, 1, @NoteDate)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources