January 13, 2009 at 4:22 pm
How do I set the value of a variable in a function to a scalar value extracted from a table?
For example, if I wanted to write a function that will get the last ClientFileID in a table and return that value, how would I do it?
I have written this piece of code and it makes sense to me:
CREATE function [dbo].[LastClientFileID] ()
returns int
as
begin
declare @LCFID as int
insert into @LCFID select top 1 CLIENTFILENUMBER from dbo.myTable order by CLIENTFILENUMBER desc
return @LCFID
end
Now, the first problem I get is that the @LCFID variable should be declared, but I obviously done so. I assume that the complaint has more to do with the fact that I did not declare is as a table value, rather than an integer value.
If I replace the select statement with an integer value I get exactly what I want, but not if I use the code as it stands.
I have tried different combinations of this, I have searched in books on line and have spent some time trolling sites trying to get the correct syntax - all to no avail.
please help.
January 14, 2009 at 3:08 am
DECLARE @LCFId int
SELECT @LCFId = MAX(ClientFileNumber)
FROM dbo.myTable
RETURN @LCFId
I hope you are not incrementing the result and writing it as the next FileNumber!
January 14, 2009 at 5:46 am
Hmm :ermm:
Yes, I have to increment.
Please don't jump on my case all at once:w00t:. I have lambasted enough DBAs about using the tools provided, but in this case I have developed an SSIS package that pulls data from a system developed using Clarion's Top Speed database, I then bulk upload the data into my system.
Once in my system, if I need to add data in I need to continue using the FileNumber created. I have set that column to be unique and I have indexed it. There is a one-to-one relationship between my auto generated primary key column that tracks all of the referential integrity, but I need to support my competitor's solution from a reporting services point of view.
By Mid Feb I hope that I will have completed all of the components that make the use of the competitor's product redundant I will no longer need this routine.
November 13, 2015 at 4:43 pm
Have you try to read them per row with "CURSOR" ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply