Recorder Ocean City,md

Perform a search in a table for each of its columns in SQL Server data

In our form we are vb.net idea of \u200b\u200bshowing a list or a grid results in a specific search of the records in a table and it would have to filter out keywords based on your type of columns they contain.

This could be very useful for those using one or a few tables in SQL Server to locate the desired records and also for those who want to show the results using a single criterion and not complicated a host of specific controls for each word or key data.

All this adds up to make a stored procedure in SQL Server in which OJO! only will the example procedure in which data would be stored and the alias name of the assigned columns in the tables or queries which will search the records, and is obvious that apart and you will have a procedure or view in the which shall consult the records.

Code in SQL Server:

CREATE PROCEDURE SP_COLUMNAS_TABLA

@ TABLE VARCHAR (20 )

AS

BEGIN

- Create a temporary table which stores NAMES OF THE COLUMNS AND CHOOSE ALIAS FOR EACH TABLE

DECLARE @ TABLA_COLUMNAS TABLE (COLUMN varchar ( 50 ) DESCRIPTION varchar (50 ))

SET NOCOUNT ON

DECLARE @COLUMNA AS VARCHAR ( 50 )

DECLARE @ARRAY_COL AS NVARCHAR ( 500 )

DECLARE @DESCRIPCION AS VARCHAR ( 50 )

DECLARE @ ARRAY_DES AS NVARCHAR (500 )

IF @ TABLE = 'PEOPLE' BEGIN

SET @ ARRAY_COL = 'COD_PERSONA, NAME, FECHA_NACIMIENTO, '- NAMES OF THE COLUMNS IN THE TABLE1 WE WANT FILTER IN THE SEARCH, THIS BOARD MIGHT HAVE oviously more columns, JUST SELECT WE WANT THE SHOW

SET @ ARRAY_DES = 'CODE, NAME, DATE OF BIRTH,' - ALIAS COLUMN IN ORDER FOR THE NAME OF THIS FORM DESCRIBE THE TIME TO SHOW IN A FORM OF CONTROL

END

IF @ TABLA = 'CIUDADES' BEGIN

SET @ARRAY_COL = ' COD_CIUDAD,NOMBRE,'

SET @ARRAY_DES = 'CODE, NAME,'

END

- you can even choose more columns in a table consultation

IF @ TABLE = 'CIUDAD_PERSONAS' BEGIN

SET @ ARRAY_COL = 'CIUDADES.NOMBRE , PERSONAS.NOMBRE '

SET @ ARRAY_DES = ' CITY, PERSON, '

END

WHILE LEN ( @ARRAY_COL ) <> 0

BEGIN

SET @COLUMNA = SUBSTRING ( @ARRAY_COL , 0 , CHARINDEX ( ',' , @ARRAY_COL ))

SET @ARRAY_COL = SUBSTRING ( @ARRAY_COL , CHARINDEX ( ',' , @ARRAY_COL , 0 )+ 1 , LEN ( @ARRAY_COL ))

SET @DESCRIPCION = SUBSTRING ( @ARRAY_DES , 0 , CHARINDEX ( ',' , @ARRAY_DES ))

SET @ARRAY_DES = SUBSTRING ( @ARRAY_DES , CHARINDEX ( ',' , @ARRAY_DES , 0 )+ 1 , LEN ( @ARRAY_DES ))

INSERT INTO @TABLA VALUES ( @COLUMNA , @DESCRIPCION )

END

SELECT * FROM @ TABLA_COLUMNAS

END

But if you are from what is not want to complicate even more with this code, and want a Porrasa all columns in a table and all the user chooses to filter the search field then I recommend using this code in SQL Server:

Select name from syscolumns WHERE id = object_id ( 'table_name' )

Well here it is clear that will not put the code vb.net to invoke this procedure and make the search string, it is up to each developer, here only gave them an idea of \u200b\u200bhow they could solve in a practical way the search in a specific table or query. The search filter would be more or less like this: