stored proc problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

stored proc problem

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-03 : 07:32:50

Hi,

i'm a problem with the following. basically i'm trying to populate a tabel held as a varaibel into which i then want to populate using a query.

alter procedure dbo.getAssets3

/* input parms */
@bomid int,
@JobNumber int = null,
@application varchar(50) = 'archive'

as

begin

/* create a variable to hold a table */
declare @assets table (
bomId int,
partId char(2),
variation char(1),
partDescription varchar(50),
releasedForDownload tinyint,
tmJobNumber int,
record_id int,
assetId uniqueidentifier,
assetName varchar(32),
fileId uniqueidentifier,
[filename] varchar(255),
path varchar(255),
fileType varchar(50),
resolution varchar(10),
trapped bit,
filetypeDescription varchar(50),
application varchar(50)
)

insert into @assets
( bomid, partid, variation, partDescription, releasedForDownload, tmJobNumber, record_id, assetId, assetName, fileId, [filename], path, fileType, resolution, trapped, filetypeDescription, application )
values
(

SELECT bomAsset.bomId, asset.partId, asset.variation, part.Description as Part, asset.releasedForDownload, asset.tmJobNumber, asset.record_id, asset.assetId, asset.assetName,
[file].fileId, [file].filename, [file].path, fileType.fileType, fileType.resolution, [file].trapped, fileType.[description], application.application
FROM
bomAsset
INNER JOIN asset ON bomAsset.assetId = asset.assetId
LEFT OUTER JOIN dam.dbo.parts part ON asset.partId = part.Id
INNER JOIN [file] ON asset.assetId = [file].assetId
INNER JOIN fileType ON [file].fileTypeId = fileType.fileTypeId
INNER JOIN application ON asset.applicationId = application.applicationId

WHERE
bomAsset.bomId = @bomid
AND ( asset.tmJobNumber is null or asset.tmJobNumber = coalesce( @jobNumber, asset.tmJobNumber ) )
AND ( application.application is null or application.application = coalesce( @application, application.application ) )

ORDER BY part.seq, asset.assetName DESC, asset.partId, asset.variation, asset.tmJobNumber

)

select * from @assets

end
GO

/*
execute dbo.getAssets
@bomid = 65647,
@JobNumber = null
*/

When i try to execute the above procedure i get the following error message and i just don't know whats wrong!

Server: Msg 156, Level 15, State 1, Procedure getAssets3, Line 43
Incorrect syntax near the keyword 'SELECT'.

Can anyone please help me. thanks

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-11-03 : 07:47:12

You do not need the keyword VALUES in your INSERT statment.


insert into @assets
( bomid, partid, variation, partDescription, releasedForDownload, tmJobNumber, record_id, assetId, assetName, fileId, [filename], path, fileType, resolution, trapped, filetypeDescription, application )
values
(

__________________
Make love not war!

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-03 : 08:42:33

thask. i got it working

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-03 : 09:13:38

hi,

i'm having another problem now!! when i try to run the procedure below get following error

Server: Msg 137, Level 15, State 2, Procedure getAssets3, Line 57
Must declare the variable '@assets'.

alter procedure dbo.getAssets3

/* input parms */
@bomid int,
@JobNumber int = null,
@application varchar(50) = 'archive'

as

begin

/* create a variable to hold a table */
declare @assets table (
bomId int,
partId char(2),
variation char(1),
partDescription varchar(50),
releasedForDownload tinyint,
tmJobNumber int,
record_id int,
assetId uniqueidentifier,
assetName varchar(32),
fileId uniqueidentifier,
[filename] varchar(255),
path varchar(255),
fileType varchar(50),
resolution varchar(10),
trapped bit,
filetypeDescription varchar(50),
application varchar(50)
)

insert into @assets
(bomid, partid, variation, partDescription, releasedForDownload, tmJobNumber, record_id, assetId, assetName, fileId, [filename], path, fileType, resolution, trapped, filetypeDescription, application )

SELECT bomAsset.bomId, asset.partId, asset.variation, part.Description as Part, asset.releasedForDownload, asset.tmJobNumber, asset.record_id, asset.assetId, asset.assetName,
[file].fileId, [file].filename, [file].path, fileType.fileType, fileType.resolution, [file].trapped, fileType.[description], application.application
FROM
bomAsset
INNER JOIN asset ON bomAsset.assetId = asset.assetId
LEFT OUTER JOIN dam.dbo.parts part ON asset.partId = part.Id
INNER JOIN [file] ON asset.assetId = [file].assetId
INNER JOIN fileType ON [file].fileTypeId = fileType.fileTypeId
INNER JOIN application ON asset.applicationId = application.applicationId

WHERE
bomAsset.bomId = @bomid
AND ( asset.tmJobNumber is null or asset.tmJobNumber = coalesce( @jobNumber, asset.tmJobNumber ) )
AND ( application.application is null or application.application = coalesce( @application, application.application ) )

ORDER BY part.seq, asset.assetName DESC, asset.partId, asset.variation, asset.tmJobNumber

select * from @assets
left outer join csa.dbo.jobs j on @assets.tmjobnumber = j.tmjobnumber
inner join csa.dbo.productlookup p on j.ProductID = p.ProductID
where @jobnumber is null and p.flags & 2 = 2

end
GO

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-11-03 : 11:28:24

Looks like your table is out of scope and is no longer in memory. Have you tried running the above in one go instead?

__________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources