>Oh no, my stored procedure has some problem .... Sorry, i Very much the problem >Question First, i want to know stored procedure can use array ? >Because my stored procedure , i want to do a update function from array to stored the field name.... >In this code, i already to do a insert function , but all field name store in @Column_List , how can i use array to call ... >for example ... @Column_List [0], @Column_List [1] to use >And i want to write a function to reuse... > >declare @Tname nvarchar(255); > declare @sql nvarchar(4000); > declare @ReplacepKey nvarchar(50); > declare @Replacedescr nvarchar(255); > >--Variable declarations >DECLARE @Column_ID int, > @Column_List varchar(8000), > @Column_Name varchar(128) > > >--Variable Initialization >SET @Column_ID = 0 >SET @Column_Name = '' >SET @Column_List = '' > >set @Tname = @tablename >--To get the first column's ID > >SELECT @Column_ID = MIN(ORDINAL_POSITION) >FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) >WHERE TABLE_NAME = @Tname > >--Loop through all the columns of the table, to get the column names and their data types >WHILE @Column_ID IS NOT NULL > BEGIN > SELECT @Column_Name = QUOTENAME(COLUMN_NAME) > FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) > WHERE ORDINAL_POSITION = @Column_ID AND > TABLE_NAME = @Tname > > --Generating the column list for the INSERT statement > SET @Column_List = @Column_List + @Column_Name + ',' > > > SELECT @Column_ID = MIN(ORDINAL_POSITION) > FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) > WHERE TABLE_NAME = @Tname AND > ORDINAL_POSITION > @Column_ID > > --Loop ends here! > END >SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1) >---------------------------------------------------------------------------------------------------------- >My second question , because i want to do a login table but the varbinary is very difficult to handle... >warning :Msg 402, Level 16, State 1, Procedure spUpdateTwoField, Line 11 >The data types nvarchar and varbinary are incompatible in the add operator. > >My code >Create procedure [dbo].[spUpdateTwoField](@tablename nvarchar(255), @pKey varchar(20), @valueOne varbinary) >as > declare @sql nvarchar(4000) > set @sql = 'update ' + @tablename + ' set password = ''' + @valueOne+ ''' where id = ''' + @pKey + '''' > execute sp_executesql @sql > > return >------------------------------------------------------------------------------------------------------------------------------------------------- > > > > >