Click here to Skip to main content
15,892,537 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
procedure basic Points
1-i have a table with primary key of type int
2-i have acolumn in this table of type uniqueidentifier its name is the same name of primarykey + the word spare
3-i use this spare column to put a uniqueidentifier depending on primary key value
the stored procedure is
SQL
Create Procedure Update_Values
@tablename as nvarchar(255),
@PrimaryKey as nvarchar(255),
@IDValue as uniqueidentifier
as
Declare @Declarestatement as nvarchar(1000)
Declare @SelectStatement as nvarchar(1000)
Declare @UpdateStatement as nvarchar(1000)
Declare @ID as int 
Declare @A as  Cursor
Set  @SelectStatement = 'Select ' + @PrimaryKey +  ' From ' + @tablename
Set  @Declarestatement = 'Set @cursor = cursor forward_only static for ' + @SelectStatement + ' open @cursor;'
Exec(@DeclareStatement)
Exec (@DeclareStatement)
while (@@fetch_status = 0)
begin
Fetch Next From A Into @ID
Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  'Where ' + @PrimaryKey + '=' + @ID
Exec(@UpdateStatement)
end
Close A 
Deallocate A


but i got the following error
SQL
Msg 402, Level 16, State 1, Procedure Update_Values, Line 18
The data types nvarchar and uniqueidentifier are incompatible in the add operator.

the line which i get the error in is
SQL
Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  'Where ' + @PrimaryKey + '=' + @ID89

the problem is @primarykey is aprimary key name
can you help me
Posted
Updated 1-Dec-14 21:34pm
v2
Comments
Shweta N Mishra 2-Dec-14 3:26am    
you should convert @IDvalue to nvarchar and then check.
oula alsheikh 2-Dec-14 6:48am    
i tried it it works as syntax
but it throws exception in execution in UpdateStatement:
Arithmetic overflow error converting expression to data type nvarchar.

1 solution

try below
SQL
Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  ' Where ' + @PrimaryKey + '=' + convert(nvarchar(36), @ID89)

or
SQL
Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  ' Where ' + @PrimaryKey + '=' + CAST(@ID89 as nvarchar )
 
Share this answer
 
v2
Comments
oula alsheikh 3-Dec-14 3:00am    
please can you give me a solution ????
DamithSL 3-Dec-14 3:27am    
have you tried both solutions? what is the error?
oula alsheikh 3-Dec-14 6:15am    
the first solution worked as follows
Set @UpdateStatement = 'Update ' + @tablename + ' Set ' + @PrimaryKey + '_Spare' + '=''' + convert(nvarchar(36),@IDValue)+ ''' Where ' + @PrimaryKey + '=' + Convert(nvarchar,@ID)

you have to add ''
thanks very much DamithSl

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900