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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy