Use Of Variable In Identity Function !

Jun 4, 2008

declare @number int

set @number = 100

SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(int, @number, 1) AS job_num,
job_lvl AS job_level,
INTO employees
FROM employee

Is there any way i can use variable inside the identity function like in the above example?.

Is there any other alternative?

Thanks in advance

View 6 Replies


Pass Variable To Identity Function

Aug 11, 2006

is it possible to pass a variable to an identity funtion


declare @max_note int

select @max_note = max(key ) from notes

select m_key = identity( int, @max_note, 1),

into #prod_note

from prod_note

View 3 Replies View Related

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes

Oct 15, 2007

I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.

Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)

It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.

Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!

Here is the code of the function in the book:

** fn_FindReports.sql
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
USE ClassNorthwind
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0

-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
RETURN -- Provides the value of @reports as the result

View 1 Replies View Related

How To Assign The @@IDENTITY To A Variable

Feb 24, 2005


HOW can I assign the value of @@IDENTITY to the any variable in SQL SERVER .


View 1 Replies View Related

IDENTITY - Using A Local Variable Value For A Seed Value

Mar 22, 1999

I'm trying to have an identity column seed value specified with a local variable value as follows, however it
doesn't allow me to do it (Says cannot use a variable name for a seed value).
Any ideas or suggestions?

DECLARE @idvalue int

SELECT @idvalue = max(accountid) + 1
FROM account

CREATE TABLE accounttemp
(Accountid int IDENTITY(@idvalue,1),
name char(10),
address char(10))

View 1 Replies View Related

Store Current Identity In A Variable

Apr 30, 2007

Hi there, I should prefix this message by saying, I am brand new to SQL so please be gentle

What I am doing seems pretty standard and I'd like some advice on the best way to proceed.

I am simply trying to add some customer information to my database. For this I have set up two tables. One for the Customer name, title, etc and then a related table for the address which is referenced in the previous table by a AddressID.

In the address table the AddressID uses an Identity. So what I'd like to do (which may not be the best way, if so tell me) is write a stored procedure to accept all of the customer information then INSERT a new entry into the Addresses table, then use DBCC to retrieve the current IDENTITY value and then add the remaining customer info into the Customers table using the IDENTITY value retrieved for the AddressID.

Now is that a total bass-ackwards way of doing this?

Thanks for the help!

View 3 Replies View Related

Using A Variable To Assign An IDENTITY SEED

Feb 16, 2004

I'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get errors so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.

View 5 Replies View Related

Return @@identity For Another Function

May 14, 2004

What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory

I cant seem to get the identity to return to my script.
This is my SP

@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)

Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)

select @@identity

I tested in query analyser, and it works fine, so It must be my code. this is my function

Sub Insert_pic(sender as object, e as eventargs)

Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))

SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)

SQLCommander.Commandtype = Commandtype.StoredProcedure

SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)

'// this section not working right, it wont write return id

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue


End sub

Can anybody see anything I missing? I appreciate any imput

View 4 Replies View Related

Restarting The Identity Column On Table Variable

Jun 2, 2006

In a cursor, I declare a table variable like so:
I then insert into that table from another table.  The purpose is to get a list that looks like this after the insert:
RowID     valueID1               348972               345223               94822
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable  DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable  variable?

View 4 Replies View Related

Has SELECT INTO With IDENTITY() Function Changed The Way #'s Are Assigned?

Oct 10, 2007

We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.

Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.

(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)

View 4 Replies View Related

Function With A Tablename As Variable

Feb 7, 2006

Dear all,

Can someone help me with the following function? I would like to use a table name as a variable.

Thanks in advance!

CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
declare @aantal numeric

if @proces = 'Inhuizen'
if @categorie = 'open_op_tijd'
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)

if @categorie = 'open_te_laat'
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)


return @aantal


View 2 Replies View Related

Static Variable In Function

Aug 30, 2006


I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?

Thank you!

View 1 Replies View Related

How Specify The Variable As Parameter For A Function

May 1, 2006

I could successfully modify the package level variable using a script component (Control Flow Level) and execute the data flow task after this script component. The OLE DB Command has one parameter for which I'm using one of the user variable. Here's the SQL statement.

SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM d_Time_School_Year
WHERE (Year_Key = ?)

This works fine. But I want to pass the year_key to a function which accepts a parameter. The SQL should be like this

SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM fn_TimeDimension (?)

But SSIS doesn't like this. When I click on parameters command button I get and error like this

"Parameters cannot be extracted from the SQL command. The provider might not help.........

Syntax error, Permission Violation, or the non-specific error(Microsoft SQL native Client)"

Any clue how to utilize the variables in a SQL which gets data from a function instead of a table?


Jemini Joseph

View 10 Replies View Related

Can A Sql 2005 Function Return More Than A Variable

Jul 30, 2007

Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance

View 3 Replies View Related

Passing A Variable To Aggregate Function

Aug 29, 2013

I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared @columnname. when i run the following it works fine

select @columnname,0,0,0,0
from temp_prt

it gives me my expected output


now i want to get the min of each column name like so

select @columnname,min(mtr_5120),0,0,0
from temp_prt ------> this works for min(mtr_5120)

now I want to generalize so I try to pass in the variable name and I do the following

select @columnname,min(@columnname),0,0,0
from temp_prt
(the columname (@columnname) exists in the table temp_prt)

but now i get an error
Msg 8114, Level 16, State 5, Line 29

Error converting data type varchar to can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:

select @columnname,'min(' + @columnname + ')',0,0,0
from temp_prt

but i get the same error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.

View 2 Replies View Related

Can't Get Left() Function To Work With A Variable.

Jan 14, 2008

I have not been able to find the answer as to why the LEFT() function doesn't see the variable as being a variable.
I originally thought it did not accept a variable as the first parameter, however the definition says it can be a variable.
Anyone knows why this isn't working?
This is how I have the code:

SELECT LEFT(@tpatdata, CHARINDEX('^', Alert1) -1)

the variable @tpatdata is the column name (tablename.Alert1), iif I rewrite it like this:

SELECT LEFT(tablename.Alert1, CHARINDEX('^', Alert1) -1) it works.

View 5 Replies View Related

Help: About Charindex Function Doesn't Work With Variable

May 23, 2007

Hello to all,
I hope that somebody can help me.
I have written a sql query to search Partner.  I have a wtcomValidRelationships Table. There are two Fields (IDMember(type: Int) and RelationshipIDs(type: varchar(1000)) in this table.
Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088. 
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex(cast(B.IDMember as char(100)),A.RelationshipIDS) > 0
and charindex(@IDO,B.RelationshipIDs) > 0
Using this query i get nothing.
I try to use constant in charindex and i get result.
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex('3422',A.RelationshipIDS) > 0
and charindex('4088',B.RelationshipIDs) > 0
So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ?
Best Regards

View 1 Replies View Related

Using A Date Function To Declare A Variable Used In A SQL Query

Feb 1, 2006

Hi all can you help me, I know that I am doing some thing wrong. What I need to do is set a variable to the current date so I can use it in a SQL query to an access database. This is what I have so far
<script runat="server"">
Sub Page_Load
dim --all the variables for my sql connections--
dim ff1 As Date
then my sql connection and queries
sql="SELECT FullRate, " & ff1 &" FROM table1 WHERE hotelnumber = " & hotel
This works is I set ff1 as a string and specify the string (my column headings are set as dates in my table)
dim ff1 As string
but I need ff1 to be the current date and is I use ff1 As date it returns time and date
Is there any way to set ff1 to the current date in this format "dd/mm/yyyy"
Any help is greatly appreciated

View 2 Replies View Related

Using Table Variable As Input To Function Or Procedure

Jul 9, 2001

Is there any way to use table variable as input to a function or stored procedure?

View 3 Replies View Related

SQL Server 2012 :: Getting A Variable Recognized In A Function

Apr 4, 2014

I am having a hard time getting a variable recognized in a function. The variable is not being seen properly in the charindex function.

@ExtType contains = X
@PhoneNo contains = +1 (202) 123-9876 X012

select @intPos = charindex(@ExtType,Upper(@PhoneNo))

View 1 Replies View Related

Passing Variable To String Compare In Function

Dec 8, 2007

I have created a function with:


ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)

DECLARE @Output varchar(100)

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity

RETURN @Output


how can I pass the variable @item correctly for the string comparison

WHERE item = @item AND week = @week

to work correctly please?

WHERE item = '@item' AND week = @week

won't work and

WHERE item = @item AND week = @week

won't work.

View 2 Replies View Related

Passing Variable To Table Function In Join

Dec 26, 2007

Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.

There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint

There is a WETPAINT table:
ItemNo int,
Color varchar(20)

There is a DRYPAINT table:
ItemNo int,
Color varchar(20)

Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType

So I created a table function called getTable:

CREATE FUNCTION [dbo].[gettable]
@ItemType int = 1
@thistable TABLE
Color varchar(20)

if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT

This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:

SELECT MAIN.ItemNo, a.Color
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo

Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.

Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.

PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.

View 2 Replies View Related

Assigning Datepart Function To A Datetime Variable?

Aug 12, 2006

I am getting wrong output when assigning a datepart function to a variable. I should get 2006 but instead I get an output 1905.

Below is the code and output. Any help will be greatly appreciated. Thanks

DECLARE @FiscalStartCurrYear datetime

SET @FiscalStartCurrentYear = DATEPART(year, GETDATE())

select @FiscalStartCurrYear



1905-06-30 00:00:00.0000

View 5 Replies View Related

Passing Table Variable To Stored Proc / Function

Nov 6, 2002

Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.


View 3 Replies View Related

Problem Passing A Variable Into A Table-valued Function

Sep 26, 2007


i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver( as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?

View 10 Replies View Related

Must Declare The Scalar Variable In Table-valued Function

May 18, 2007

Hi, I'm having trouble with this multi-statement table-valued function:

-- Add the parameters for the function here
@dateStart DATETIME,
Insurer VARCHAR(50),
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid91120 MONEY DEFAULT 0,
Insurer VARCHAR(50),
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid91120 MONEY DEFAULT 0,

SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
WHERE Pims.Insurer = @arTemp.Insurer AND
Pims.NABP = @arTemp.NABP AND
Pims.NumRx = @arTemp.NumRx


I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View 10 Replies View Related

DB Engine :: How To Get Multi-select Value In A Variable In Server Function

Jun 1, 2015

i have a column with mulitple ids stored with commas . i want to pass ids and get data along with name from the to get multiselect value in a variable in  sql server function 

View 4 Replies View Related

SQL Server 2014 :: User Defined Function - Using Table Name As Variable

Aug 9, 2014

I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...

CREATE FUNCTION count_rows (@tablename varchar(100)
DECLARE @emp_count AS int
declare @declaration varchar(100)

[Code] ....

The errors I am getting are as follows:

Msg 102, Level 15, State 1, Procedure count_rows, Line 3
Incorrect syntax near 'RETURNS'.
Msg 102, Level 15, State 1, Procedure count_rows, Line 10
Incorrect syntax near '@declaration'.
Msg 178, Level 15, State 1, Procedure count_rows, Line 14

A RETURN statement with a return value cannot be used in this context.

View 9 Replies View Related

Transact SQL :: How To Turn Select Aggregate Function Statement Into A Variable

May 26, 2015

I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?

Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;

View 11 Replies View Related

Transact SQL :: Use Print Function To Output Numeric Variable With Fixed Amount Of Leading Zeroes

Apr 23, 2015

I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string.  For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.

Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.

View 4 Replies View Related

Indexes On Table Variable Of Table Valued Function

Jan 6, 2004

Hi there,

Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
@param1 int,
@param2 int
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff


View 2 Replies View Related

Table-valued Function Run Once For Each Row In A Table Variable.

Mar 19, 2008

I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.

Code Snippet

declare @IdTable table
EmployeeId nvarchar( 16 ) not null
insert into @IdTable
select EmployeeNumber from Employees

I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
select * from fn_GetEmployeeById( EmployeeId )

Any help is very much appreciated.

View 3 Replies View Related

Table-valued Function Into A @table Variable

Feb 22, 2008

In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?



@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'


--split CourtIDs into a table
select * from dbo.Split(@CourtIDs, ',')

View 6 Replies View Related

Copyrights 2005-15, All rights reserved