Use Of Variable In Identity Function !

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,
pub_id,
hire_date
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



ADVERTISEMENT

Pass Variable To Identity Function

is it possible to pass a variable to an identity funtion

example

declare @max_note int

select @max_note = max(key ) from notes

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

into #prod_note

from prod_note

View Replies View Related

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

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
GO
/*
**  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))
RETURNS @reports TABLE
  (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
AS
BEGIN
 DECLARE @RowsAdded int
 -- Initialize @reports with direct reports of the given employee
 INSERT @reports
  SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
  FROM EMPLOYEES
  WHERE ReportsTo = @InEmployeeID
 SET @RowsAdded = @@rowcount
 -- While new employees were added in the previous iteration
 WHILE @RowsAdded > 0
 BEGIN
  -- 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
 END
RETURN -- Provides the value of @reports as the result
END
GO
 

View Replies View Related

How To Assign The @@IDENTITY To A Variable

Hi,

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

Thanks,

View Replies View Related

Return @@identity For Another Function

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

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

AS
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)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)

select @@identity
return
GO


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

response.write(SQLCommander.Parameters("ReturnValue").Value)

SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()

End sub

Can anybody see anything I missing? I appreciate any imput

View Replies View Related

Reset Identity Value On A Table Variable

Hi,

Is anyone familiar with a way to reset the identity value on a table variable...

the DBCC command doesnt seem to work on a table variable...

I try this:
dbcc checkident ('@block, RESEED, 1)

I get this msg, rightfully so..

Could not find a table or object named '@block'. Check sysobjects.

My block table has an identity column called "blockid". Is there anyway I can update this column after the inserts have been done? Or does anyone know of any other way I can use the identity capability on a table variable?

Thanks much :)

View Replies View Related

Using A Variable To Assign An IDENTITY SEED

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 Replies View Related

IDENTITY - Using A Local Variable Value For A Seed Value

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 Replies View Related

Store Current Identity In A Variable

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 Replies View Related

Restarting The Identity Column On Table Variable

In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
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
etc....
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 Replies View Related

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

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 Replies View Related

Function With A Tablename As Variable

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
BEGIN
declare @aantal numeric

if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
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)
end

if @categorie = 'open_te_laat'
begin
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)
end

end

return @aantal

END

View Replies View Related

How Specify The Variable As Parameter For A Function

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?

Thanks

Jemini Joseph

View Replies View Related

Can A Sql 2005 Function Return More Than A Variable

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 Replies View Related

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

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 Replies View Related

Help: About Charindex Function Doesn't Work With Variable

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 ?
Thanks
Best Regards
Pinsha

View Replies View Related

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

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
ff1="30/01/2006"
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 Replies View Related

Can We Use A Variable As An Argument In A User Defined Function

Hi,
I have written a user defined function to convert a date from an OLD time zone to NEW time Zone. The three parameters are (date, OLDTz,NEWTZ).

=====================================
declare
@v_tz varchar(10) ,
@v_da integer,
@v_ss varchar(100)
select @v_da=7
select @v_tz='GMT'
select @v_ss='soumilpatel'
select substring('soumilpatel',2,3)
select substring(@v_ss,2,3)
select getdate(), dateadd(day, 7, getdate()), dateadd(day, @v_da, getdate())
select top 2 timestampwhenset, dbo.tc(timestampwhenset,'GMT','PST') from s_monitorevents
select top 2 timestampwhenset, dbo.tc(timestampwhenset,'GMT', @v_tz ) from s_monitorevents
go
=====================================

In real life the value of @v_tz is coming from a table. How do I go about resolving the value of the variable before I run the function. I was able to use the variable substitution successfully in a numeric function as well as in a string/character function.

Your input is very much appreciated.
-soumil

View Replies View Related

Using Table Variable As Input To Function Or Procedure

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

View Replies View Related

Passing Variable To Table Function In Join

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
)
RETURNS
@thistable TABLE
(
Color varchar(20)

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

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
FROM MAIN
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 Replies View Related

Passing Variable To String Compare In Function

I have created a function with:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN

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


END

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 Replies View Related

Make A Select From A Clr Function To A Table Variable

Hi,
is it somehow possible to make a Select-Statement from a Clr-Funktion to a table variable??
 
Regards
Pamelia

View Replies View Related

Assigning Datepart Function To A Datetime Variable?

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

 

Output

-----------

1905-06-30 00:00:00.0000

 

View Replies View Related

Problem Passing A Variable Into A Table-valued Function

Hi,

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.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) 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 Replies View Related

Passing Table Variable To Stored Proc / Function

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.

TIA,

View Replies View Related

&&"Must Declare The Scalar Variable&&" In Table-valued Function

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

ALTER FUNCTION MakeArDetail
(
    -- Add the parameters for the function here
    @dateStart DATETIME,
    @dateEnd DATETIME
)
RETURNS @arDetail TABLE 
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)

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

INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO

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 Replies View Related

Indexes On Table Variable Of Table Valued Function

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)
AS
BEGIN

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff

RETURN
END

View Replies View Related

&&"Static Variable&&" In Function

Hi!

     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 Replies View Related

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

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.
Andrew
 

View Replies View Related

Table-valued Function Into A @table Variable

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?




ALTER   PROCEDURE [dbo].[rpt]

(


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

)
AS


--split CourtIDs into a table
DECLARE @tbCourtIDs     table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')

View Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table). 

What is the most direct way to do this in SSIS?

 

TIA,

 

barkingdog

 

P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

 

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved