Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Sql2005 EncryptByPassPhrase binary problem
Message
From
03/01/2011 22:27:31
 
 
General information
Forum:
ASP.NET
Category:
Coding, syntax and commands
Environment versions
Environment:
VB 9.0
OS:
Windows XP
Database:
MS SQL Server
Miscellaneous
Thread ID:
01494217
Message ID:
01494538
Views:
44
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 
-------------------------------------------------------------------------------------------------------------------------------------------------
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform