i have the following stored procedure that makes many tasks according to my situation
my problem is not in the stored procedure because it works fine in sql and does what i want
but my problem is in calling this stored procedure in C#.net (Ado.net technique)
because when i call the procedure i got the following exception
Either the parameter @objname is ambiguous or the claimed @objtype (Column) is wrong.
calling stored procedure code is
public void CreatePrimaryKeyOnly(string PT,string PK)
{
using (SqlConnection connection = new SqlConnection(connection_string))
{
connection.Open();
SqlCommand command = new SqlCommand("CreatePrimaryKeyOnly", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@TableName", SqlDbType.VarChar).Value = PT;
command.Parameters.Add("@ColumnName", SqlDbType.VarChar).Value = PK;
command.ExecuteNonQuery();
connection.Close();
}
}
and the stored procedure is
ALTER Procedure [dbo].[CreatePrimaryKeyOnly]
@TableName nvarchar(255),
@ColumnName nvarchar(255)
as
Declare @AddColumn nvarchar(2000)
Set @AddColumn='Alter Table [' + @TableName + '] Add ' + @ColumnName + '_Spare uniqueidentifier Not null Default(newid()) '
Exec(@Addcolumn)
Declare @GetPrimayKeyName nvarchar(2000)
SELECT @GetPrimayKeyName = 'ALTER TABLE [' + @TableName
+ '] DROP CONSTRAINT ' + name + ';'
FROM sys.key_constraints
WHERE [type] = 'PK'
AND [parent_object_id] = OBJECT_ID(@TableName);
EXEC sp_executeSQL @GetPrimayKeyName
Declare @JoinRenameStatement nvarchar(1000)
Set @JoinRenameStatement='Sp_rename ''['+@TableName+'].'+@ColumnName + ''' , ForJoin,''Column'''
Exec(@JoinRenameStatement )
declare @strsql varchar(2000)
set @strsql = 'Sp_rename ''['+@TableName+'].'+@ColumnName+ +'_Spare'+ ''' , '''+@ColumnName+''',''Column'''
exec (@strsql)
declare @PrimaryKeyStatement varchar(2000)
Set @PrimaryKeyStatement= 'Alter Table ['+ @TableName + '] Add Constraint ' + @ColumnName + 'PK Primary Key(' + @ColumnName + ')'
Exec(@PrimaryKeyStatement)
i tried this to put the parameter in calling stored procedure as SqlDbType.NVarChar but it doesnt work too
please any solution?