|
I am drawing a blank on this and i have non eo fmy samples where i am, so i cant for the life of me remembwer how to do this, but...
When doing an Insert into a table that has Identity Seed turned on for the primary key, how do you return the id of the new row?
i know you end the insert statement, then it is somehting like this:
Insert Into dbtable_x (Stuff) Values(@stuff); @selectID
I knwo im not that far off the mark, but computers are some damn picky about syntax....
oooookie dokie.... select @@Identity
At least i figured it out...
-- modified at 13:00 Monday 2nd October, 2006
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
You're safer with using SCOPE_IDENTITY() instead of @@Identity
Below is an excerpt from the SQLTeam:
The gist: Use scope_identity() rather than @@IDENTITY or you might not get what you're looking for.
"ManyTSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application.
@@Identity is potentially a very, very bad thing! In almost every case, you should use scope_identity() instead.
Why? @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
Dear CPians,
I need to keep a mirror table of an existing table within the same database.
I need my clone to reflect to the original one.
I need to have all inserts and updates but NO deletes!
Has anyone got any ideas of how should I do that?
Thanking you in advance.
|
|
|
|
|
Triggers on the source table to copy the changes to the mirrored table.
|
|
|
|
|
Hi All,
I need to send BIGINT values as a Parameter for IN Tag.But My Example is not working .It says
Error converting data type varchar to bigint.
please check the below tested sample:-
DECLARE @UidAgentGroupID VARCHAR(100)
SET @UidAgentGroupID='1,2,3,4'
SELECT markup_id FROM Agent_Markup_Fare WHERE UidAgentGroupID IN(@UidAgentGroupID)
Then How can I send BigInt values(more than one) to IN Tag(IN(@UidAgentGroupID)) ? I can not use while loop for this senario..
Can you please help me to solve this?
Thanks in Advance.
|
|
|
|
|
The SQL compiler uses your comma delimited string as a single variable. It can't find a way to convert it to the Big integer it needs. It doesn't embed the string into your sql statement to compose a new statement.
You must use dynamic SQL to solve these types of issues. This allows you to build the statement you desire on the fly.
DECLARE @UidAgentGroupID VARCHAR(100)
SET @UidAgentGroupID= N'1,2,3,4'
DECLARE @sql NVARCHAR(1000)
SET @sql = N'SELECT markup_id FROM Agent_Markup_Fare
WHERE UidAgentGroupID IN(?INVARIABLES?)'
SET @sql = REPLACE(@sql,'?INVARIABLES?',@UidAgentGroupID)
EXEC(@sql)
The REPLACE statement can be replaced with simple string concatination. I find that the REPLACE method is easier to read and therefore, more maintainable. It can save you a lot of debug time when you are using multiple replacement variables.
|
|
|
|
|
Hi,
Many Thanks for reply.
I need to send Parameter Values for SQL Statement.I am using SQL Server 2000
So I have tried like this:
DECLARE @sqlStatement VARCHAR(1000)
SET @sqlStatement='SELECT @adultmarkup=SUM(dbo.CalculateMarkup(@fareprice,adultMarkup)),@childmarkup=SUM(dbo.CalculateMarkup(@fareprice,childMarkup)),@infantmarkup=SUM(dbo.CalculateMarkup(@fareprice,infantMarkup)),@studentmarkup=SUM(dbo.CalculateMarkup(@fareprice,studentMarkup))
FROM Agent_Markup_Fare WHERE fareid=@fareid AND (AgentsSubGroupID IN(INVARIABLES))'
SET @sqlStatement = REPLACE(@sqlStatement,'INVARIABLES',@AgentPreferencesIDS)
EXEC(@sqlStatement)
But Error Message Appeared :-
Must declare the variable '@fareprice'.
But All the Variables are declared on the Stored Procedure.
Can you please tell me how to recover the problem?
Thanks in advance.
|
|
|
|
|
Consider the Execute call to be a different function call. It is not aware of the local variables in your stored procedure. Therefore, they are undefined inside of the execute. Try something like this:
DECLARE @sqlStatement NVARCHAR(1000)
SET @sqlStatement='SELECT AgentsSubGroupID
FROM Agent_Markup_Fare
WHERE AgentsSubGroupID IN(INVARIABLES)'
SET @sqlStatement = REPLACE(@sqlStatement,'INVARIABLES',@AgentPreferencesIDS)
CREATE TABLE #tmpTbl (AgentsSubGroupId INT NOT NULL)
INSERT INTO #tmpTbl
EXEC(@sqlStatement)
SELECT
@adultmarkup=SUM(dbo.CalculateMarkup(@fareprice,adultMarkup)),
@childmarkup=SUM(dbo.CalculateMarkup(@fareprice,childMarkup)),
@infantmarkup=SUM(dbo.CalculateMarkup(@fareprice,infantMarkup)),
@studentmarkup=SUM(dbo.CalculateMarkup(@fareprice,studentMarkup))
FROM
Agent_Markup_Fare
WHERE
fareid=@fareid AND
AgentsSubGroupID IN (SELECT AgentsSubGroupID FROM #tmpTbl)
DROP TABLE #tmpTbl
This turns your CSV list into a temporary table. This allows you to use the set based operations in SQL as normal. It may not be optimal but, it should be enough to get you started in the right direction. Don't forget to drop the table when you are done with it. It should have a connection scope but, cleanup is always advised.
|
|
|
|
|
Hello database gurus,
Say I have a table 'List' which represents a forward-only linked list:
Id NextId Text
---------------------------------
1 6 This is item 1
2 5 This is item 5
3 10 This is item 7
4 2 This is item 4
5 3 This is item 6
6 8 This is item 2
7 9 This is item 9
8 4 This is item 3
9 NULL This is Item 10
10 7 This is Item 8
and I want to create a query which outputs 'This is item 1', 'This is item 2'..... down to 'This is item 10', what's the best way to do this?
I've managed to do it with recursion, but this can't be good for long lists. What about using a cursor or some clever index? Any ideas? Thank God I've never been asked anything like this at interview...
Regards,
Rob Philpott.
|
|
|
|
|
This isn't really what SQL is desgined to do. If you already know the order in which the rows are supposed to come, why not add an "OutputOrder" column then user ORDER BY OutputOrder
Assuming the start is always Id=1
DECLARE @Id int;
SELECT @Id = 1;
WHILE @Id IS NOT NULL
BEGIN
-- Do stuff you need to do here.
-- The next line moves to the next item in the linked list.
SELECT @Id = NextId FROM MyTable WHERE Id = @Id;
END
Please note, that this is incredibly inefficient. You might want to use this to populate an OutputOrder column which you can then use later to ensure the output is efficiently selected in the correct order.
|
|
|
|
|
Thanks for the reply Colin,
Why do you say this isn't really what Sql is designed to do? It seems perfectly acceptable, desirable even (well to me at least!) to have an abstraction where a database can maintain a linked list of data. My operations would be 'return in order', 'delete item in list', 'insert item in list'.
As you suggest, you could have a column which maintains the order, but then inserts and deletes would be slow because you'd need to update this field in all later records. I guess what I'm looking for is the solution with a good worst-case insert/delete/select.
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: Why do you say this isn't really what Sql is designed to do? It seems perfectly acceptable, desirable even (well to me at least!) to have an abstraction where a database can maintain a linked list of data. My operations would be 'return in order', 'delete item in list', 'insert item in list'.
Because SQL is a set based language. It operates more efficiently on small numbers of large set operations rather than large numbers of small set operations.
Traversing a linked list requires a large number of small set operations. e.g. Each SELECT is a set based operation. If you have to traverse a linked list you need to perform a large number of select operations. If you have an Order column in the table then you just need one SELECT statement with an ORDER BY clause.
Remember also, that a linked list is really just an implementation detail of one way to store a set of data. It is useful in certain scenarios. I used linked lists many-a-time when I programmed in C++, but I also used arrays when the need arose.
To get around the insert and delete problem while holding an Order column. the Order column could increment by 2. An insert would put the new row with an odd number (first set based operation), Immediately after, if you are using SQL Server 2005, you could renumber the Order column by using the row numbering feature and multiplying the result by 2 to get the new value of the Order column (second set based operation). Delete operations don't actually have to re-order anything as the Order column is just for ordering and nothing else. Insert operations now trade off some of the speed they once had (because large tables will still take some time) for much faster speed on iteration.
As it stands the WHILE loop suggestion I gave before will have (on anything more than a handful of rows) unacceptable iteration performance when getting the data out. (Large numbers of small set operations)
|
|
|
|
|
Thanks again for you input Colin,
I guess when you reach a situation like this it's probable that the model isn't quite right and needs a rethink. In my experience, everything fits nicely into place when done properly, and this doesn't sit well!
What I was trying to achieve was a generic note facility, where users could add notes to different things. eg. in a database I might have tables Car, Book, Person, Tree representing the objects I'm storing data about. In each of these tables I wanted a 'NoteId' column which could point to the first of a list of notes which could be added and removed at will by users. This way the same note table could be used by different objects.
I suppose the other more traditional way of doing this is via use of one or more intermediate tables which map one-to-many objectIds to Notes eg. CarNotes, BookNotes, PersonNotes, TreeNotes, and I guess this is good because it allows FK constraints to be implemented, but I just wanted to avoid these extra tables. They're almost 'clutter'.
What's the weather like in Scotland? It's been a bit stormy down in London over the past few days.
Regards,
Rob Philpott.
|
|
|
|
|
From what you describe I don't think you need intermediate tables. A compound primary key on the note table might suffice, assuming you don't have one note that needs to be shared among multiple other tables.
The MainId would be what other tables use to link to the notes - so it uniquely defines a group of notes. The SecondaryId is just to uniquely identify each note within the group.
Perhaps that might work. If your system is going to be busy and there may be the scenario where two processes want to create a new group of notes simultaneously then I'd recommend a GUID for the MainId because they won't collide as you cannot use an Identity because it would cause problems when you wanted to put a second note in an existing group and you can't use MAX(MainId) because two processes might try the same thing at the same time.
Your other option is intermediate tables. I don't really think they clutter up the place, but if your think they do, then you could always prefix their name with something to keep them out of the way.
|
|
|
|
|
Rob Philpott wrote: What's the weather like in Scotland? It's been a bit stormy down in London over the past few days.
The rain this afternoon was magnificent. It was almost Mediterranian in its ferocity. It was the same yesterday afternoon just as the sun was setting.
|
|
|
|
|
HI
Guys can any one let me know, which is the largest data type in SQL Server, plz reply ASAP,thankx in advance.
|
|
|
|
|
|
"image" max length 2^31-1 bytes
Note that starting at SQL Server 2005, "image" is deprecated.
Use "varbinary(max)" (max length 2^31-1 bytes) instead.
Mark
|
|
|
|
|
Sir,
I am trying to create a stored procedure in the database in sql server 2005.I have written for the stored procedure .But when i am trying to save it , then the saved stored procedure file doesn't come under the stored procedure hierarchy.and abother problem is when i try to access the stored procedure using asp.net2.0 I mean the saved stored procedure it says the stored procedure i am accessing does not exist.
Please help.
|
|
|
|
|
VS 2003/2005,
when you edit a procdure, "save" means create/alter
SQL Server 2005 Management Studio,
if you want to create a procedure, writing code first then run and create the proc
|
|
|
|
|
I am adding articles to be replicated but I would like to use a cursor to do it due the amount of tables it may take forever to select all from the wizard. I have written a cursor and I am having issues running it. Any suggestion will be greatly appreciated.
Cursor:
declare @tableName varchar(100)
declare c cursor for
select
table_name
from
information_schema.tables
where table_type = 'base table'
open c
fetch next from c into @tableName
while @@fetch_status = 0
begin
--print 'table name: ' + @tableName
--Adding the transactional articles
exec sp_addarticle @publication = N'One_Way_Trans', @article = @tableName , @source_owner = N'dbo', @source_object = @tableName, @destination_table = @tableName, @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N'false', @ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName, @filter = null, @sync_object = null, @auto_identity_range = N'false'
fetch next from c into @tableName
end
close c
deallocate c
I am getting the following error when I run this script: Line 99: Incorrect syntax near '+'.
Here are the values I can not populate with @tableName.
@ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName,
PS: I got the code below to do exactly what I want it to do but notice it is only printing the reults not executing the stored procedure.
declare @tableName varchar(100)
declare c cursor for
select
top 10 table_name
from
information_schema.tables
where table_type = 'base table'
open c
fetch next from c into @tableName
while @@fetch_status = 0
begin
--print 'table name: ' + @tableName
--Adding the transactional articles
print 'exec sp_addarticle @publication = N''One_Way_Trans'', @article = N ''' + @tableName + ''' , @source_owner = N''dbo'', @source_object = N''' + @tableName + ''', @destination_table = N'''+ @tableName + ''', @type = N''logbased'', @creation_script = null, @description = null, @pre_creation_cmd = N''drop'', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N''false'', @ins_cmd = N''CALL sp_MSins_' + '' + @tableName + ''', @del_cmd = N''CALL sp_MSdel_' + ''+ @tableName + ''', @upd_cmd = N''MCALL sp_MSupd_' + '' + @tableName + ''', @filter = null, @sync_object = null, @auto_identity_range = N''false'''
fetch next from c into @tableName
end
close c
deallocate c
-- modified at 22:23 Friday 29th September, 2006
Greg
Coding makes the world go round!!!
|
|
|
|
|
Never Mind, I got it working. What I did was I ran the "print" script, pulled copied the result and rn it in Quer Analzer. It ran just fine and created all my Articles.
PS: I could not believe I was such a dork. But then again no where tells you that you need to run one script to create another.
Thanks guys.
OBTW: It would be nice if someone can explain wh I can not add a variable name to a stored procedure dnamically.
Greg
Coding makes the world go round!!!
|
|
|
|
|
how can i save the value of a field into a parameter in T-SQL?what i wrote is-
Create Procedure a_c
@C_Name varchar(100),
as
if exists(
select id as 'ID' from ABC where C_Name = @C_Name)
begin
----
end
else
begin
---
end
go
is this syntax correct?and should i declare 'ID'and how can i do it?
|
|
|
|
|
-- create proc
create proc abc
@para int output
as
set @para = 1
go
--exec proc
declare @p int
exec abc @p output
select @p
|
|
|
|
|
 I have this info
Desc1................Desc2
-------------......----------
BudgetControl......RamonDilan
DirectorContr......JoseAleman
I wanna to show
Desc1................Desc2.............Desc1.............Desc2
-------------......----------......-------------...---------
BudgetControl......RamonDilan......DirectorContr...JoseAleman
Please help me on that.. Thanks
johnny
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
