Chris writes "I am trying to generate a sequence from a select statement.For each record that the select returns I am looking for an incremented value.**********************************************Example Outut:40000000021125 1 012050 1200040000000021125 2 013310 1500**********************************************
Values 1 and 2 is an inline row counter.I have acheived this result in a way that is clunky using the following code:*************************************CREATE TABLE #Q (TRANSACTIONID BIGINT NOT NULL, AUTOID INT IDENTITY(1,1) PRIMARY KEY,PRODUCTCODE VARCHAR(10) NOT NULL,PRICE FLOAT NOT NULL )INSERT INTO #QSelect TransactionId, ProductCode,(price*100) as Price from transactionItem WITH (NOLOCK) where TransactionId = 19SELECT * FROM #QDrop Table #Q*************************************
I figure there has to be away to do this with a function. I tried the following code only to get a large list of 1's in sequence column*******************************************************CREATE FUNCTION INCREMENTINC(@INCR INT) RETURNS INTAS BEGIN RETURN (@INCR + 1)END ****************************************************************************************************DECLARE @INC INT SET @INC = 0SELECT dbo.INCREMENTINC(@INC)SELECT dbo.INCREMENTINC(@INC)********************************************OUTPUT=11What I wanted was output of 12
as I would be using the function in a "insert into table x select..." kind of statementIf the function allowed us to pass the variable in by reference (like in most programming languages, C#, VB, C++, Delphi,JAVA)this would work. How do I accomplish this in a function in TSQL?ThanksChris"
User Defined Functions Passing in a variable by ref 
Posted - 2006-03-17 : 08:21:02
