March 14, 2008 at 9:01 am
Does anybody has any expertise in this.
So far i have created the folowing script but i goes wrong when inserting the colums string into the dynamic sql
as parameters i use :
the tablename
the columname on witch to select
the id for the select
as return i would love the new value for the columname
the stored procedure
alter PROCEDURE [dbo].[TableRowCopy] (
@TableName varchar(50),
@IdColumname varchar(50),
@WhereIdValue int,
@NewId int OUTPUT
)
AS
BEGIN
declare
@columns varchar(5000),
@query varchar(8000)
set @query = '';
select @columns = case when @columns is null then column_name else @columns + ',' + column_name end
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Ltrim(RTRIM(@TableName))'
and column_name <> 'Ltrim(RTRIM((@IdColumname))'
set @query = 'insert into ' + @TableName + ' ('' + @columns + '') SELECT '' + @columns + '' FROM ' + @TableName + ' WHERE ' + @IdColumname + ' = @WhereIdValue '
exec (@query)
SELECT @NewId = Scope_Identity()
return @NewId
END
The calling :
declare @newid int
exec dbo.TableRowCopy 'appversie','id',2, @newid
select @newid;
This returns NULL
For as far is i can tell, there is some fault when inserting the @columns string into the dynamic query
Some extra check that should be performed is to check whether a column is also an identity field so that we can exclude that from the insert and select statement,
for the moment we do not have tables with more than 1 identity field but you never know for the future
I think that if we get this script running it could serve for many people 😉
Tnx a lot in advance for any help.
wkr,
Eddy
March 14, 2008 at 9:10 am
The first thing I noted was the way you were calling the stored procedure:
exec dbo.TableRowCopy 'appversie','id',2, @newid
the last parameter is an output parameter, so you should call it:
exec dbo.TableRowCopy 'appversie','id',2, @newid OUTPUT
Regards,
Andras
March 14, 2008 at 9:11 am
You raised an interesting question.
Duplicates in a database is against the concept of database normalization. If you really would like to retrieve data in one way or another of duplicates, you may try to use UNION clause. You can add your modification in your queries.
March 14, 2008 at 9:21 am
Hello
No, its not for retrieving of data,
But for some application elements it would be very handy to copy a complete row of data
and then alter some values of the new record via the userapplication,
This because we have some fiches that contains over 40 values (in 1 row)
and a copy and make a change on 1 or 2 values is much easier then make a new input for the +40 values
and that way i was thinking, maybe it can serve also for other purposes..
therefor i put this as a question, but as i said, there is something wrong with the concatenation of the dynamic query..
wkr,
Eddy
March 14, 2008 at 9:23 am
Another thing in your query is the
where TABLE_NAME = 'Ltrim(RTRIM(@TableName))'
and column_name <> 'Ltrim(RTRIM((@IdColumname))'
First, you are checking if the table name is the string "Ltrim(RTRIM(@TableName))". Rather uncommon names for a table. Even if it were evaluated, the parentheses are not matched.
Try:
where TABLE_NAME = Ltrim(RTRIM(@TableName))
and column_name <> Ltrim(RTRIM(@IdColumname))
Regards,
Andras
March 14, 2008 at 9:24 am
Try updating the column list build to put brackets around the column names. You never can tell when you might run up against a "reserved word" used as a column name, or something with a space in it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 9:27 am
Ok, a version that does something (although it is weird, you do assume that the id column is an identity column for exmaple) is:
ALTER PROCEDURE [dbo].[TableRowCopy]
( @TableName VARCHAR(50)
, @IdColumname VARCHAR(50)
, @WhereIdValue INT
, @NewId INT OUTPUT)
AS
BEGIN
DECLARE @columns VARCHAR(5000)
, @query VARCHAR(8000)
SET @query = '' ;
SELECT @columns = CASE WHEN @columns IS NULL THEN column_name
ELSE @columns + ',' + column_name
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = LTRIM(RTRIM(@TableName))
AND column_name <> LTRIM(RTRIM(@IdColumname))
SET @query = 'insert into ' + @TableName + ' (' + @columns
+ ') SELECT ' + @columns + ' FROM ' + @TableName + ' WHERE '
+ @IdColumname + ' = ' + CAST(@WhereIdValue AS VARCHAR)
PRINT @columns
EXEC (@query)
SELECT @NewId = SCOPE_IDENTITY()
END
Regards,
Andras
March 14, 2008 at 2:22 pm
As noted above, you should definitely put brackets (QUOTENAME()) around your column names in the column list generation.
March 15, 2008 at 3:24 am
Hello Andras,
Idd, the routine you provided does what i wanted it to do,
except i do not get the new id for the identity columns
it executes the insert but doen not provide the return value instead it returns NULL
You where right, i always exspect the select column to be the identity column
witch is what we have agreed with the application develompent team so thats not really an issue
Im goin to try figuring out where the indentity result is lost..
tnx a lot everybody for helping out with the script, all the feedback is really appreciated..
wkr
Eddy
March 15, 2008 at 11:45 am
But for some application elements it would be very handy to copy a complete row of data
and then alter some values of the new record via the userapplication,
You really need to be careful doing that... doing an insert of the copy and then doing an update is going to cause both INSERT and UPDATE triggers to fire. If there are UPDATE triggers on the table, then you need to create the new row in a temp table, do the UPDATEs there, and then insert the fully formed row into the final table.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2008 at 4:29 am
Hello Jeff,
Thats not an issue,
Its the idea of (from application development view)
To have a easy way of copying a record, (inserting into the database)
Get the id of the new inserted record back (identity)
and then use the inserted record (with the returned identity) in the application for making a modig-fication from the application out witch they then can update as a normal record.
I have asked them also why they can not just make a rowcopy in the application dataset as like you can do with the .net environment but they use "windev" witch is based on a sort of visual basic and they seem not to find a "easy" method for accomplisching that..
Therefor they asked me of it could not been accomplisched by the use of the database..
Witch should be able to do just that if i just find a way of returning the identity of the new inserted row... but sofar it makes the insert but returns NULL for the new row..
why scope_identity() is not working i have no idea..
Wkr
Eddy
June 6, 2008 at 7:17 am
It looks like it's not returning the ID because it's not in scope from EXECing the query in the SP, the following works for it, but you're limited to 4000 for the Nvarchar column and query variables....
ALTER PROCEDURE [dbo].[TableRowCopy]
( @TableName VARCHAR(50)
, @IdColumname VARCHAR(50)
, @WhereIdValue INT
, @NewId INT OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @columns NVARCHAR(4000)
, @query NVARCHAR(4000)
SET @query = '' ;
SELECT @columns = CASE WHEN @columns IS NULL THEN '[' + column_name
ELSE
@columns + '],[' + column_name
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = LTRIM(RTRIM(@TableName))
AND column_name <> LTRIM(RTRIM(@IdColumname))
SET @columns = @columns + ']'
SET @query = 'insert into ' + @TableName + ' (' + @columns
+ ') SELECT ' + @columns + ' FROM ' + @TableName + ' WHERE '
+ @IdColumname + ' = ' + CAST(@WhereIdValue AS VARCHAR) + ' SELECT @IDOUT= SCOPE_IDENTITY()'
PRINT @columns
EXECUTE sp_executesql @query,N'@IDOUT AS INT OUTPUT', @IDOUT = @NewID OUTPUT
RETURN @NewId
END
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply