EXCLUDE COLUMN IN SQL SERVER

In one of my project i want a select query on table which have bunch of columns and want to exclude a column "Reason" from them.

Query

DECLARE @tableName SYSNAME,


@excludeColumnName SYSNAME,

@whereClause VARCHAR(MAX)



SET @tableName = 'EXPCONTACT'

SET @excludeColumnName = 'REASON'



DECLARE @query NVARCHAR(MAX)

DECLARE @select VARCHAR(MAX)



select @select = COALESCE(@select + ',','') + COLUMN_NAME

from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = @tableName and COLUMN_NAME <> @excludeColumnName

order by ORDINAL_POSITION



set @query = 'SELECT ' + @select + char(13) +

'FROM ' + UPPER(@tableName) +

case when @whereClause is not null then char(13) + 'where ' + @whereClause else '' end



print @query

exec sp_executesql @query

PROPERSTATEMENT function in sqlserver to proper statement like excel

I find in one of my project to use some function like proper in excel which i already find from google. But i am having bunch of record to proper even want to sepearate some words in the sentence.

e.g.
query : select properstatement('asifghanchi','asif')
output : asif ghanchi

Description : asif is word to seperate and apply proper to whole statement.

Code:

USE [DATABASE]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERSTATEMENT] Script Date: 08/04/2010 11:05:22 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROPERSTATEMENT]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[PROPERSTATEMENT]
GO

USE [DATABASE]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERSTATEMENT] Script Date: 08/04/2010 11:05:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[PROPERSTATEMENT]
(
@input varchar(8000),
@properinput varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
IF (LEN(@properinput) = 0 OR LEN(@input) = 0)
RETURN DBO.PROPERCASE(@input)
ELSE IF (CHARINDEX(',',@properinput) = 0 AND CHARINDEX(@properinput,@input) = 0)
RETURN DBO.PROPERCASE(@input)
ELSE IF (CHARINDEX(',',@properinput) = 0 AND CHARINDEX(@properinput,@input) = 1)
RETURN DBO.PROPERCASE(@properinput) + ' ' + DBO.PROPERCASE(REPLACE(@input,@properinput,''))
ELSE IF (CHARINDEX(',',@properinput) = 0 AND CHARINDEX(@properinput,@input) > 1)
RETURN DBO.PROPERCASE(SUBSTRING(@input,1,CHARINDEX(@properinput,@input)-1)) + ' ' + DBO.PROPERCASE(@properinput) + ' ' + DBO.PROPERCASE(SUBSTRING(@input,CHARINDEX(@properinput,@input)+LEN(@properinput),LEN(@input)))
ELSE IF CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) = 0
RETURN [dbo].[PROPERSTATEMENT](@input,SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput)))
ELSE IF CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) = 1
RETURN DBO.PROPERCASE(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1)) + ' ' + [dbo].[PROPERSTATEMENT](REPLACE(@input,SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),''),SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput)))
ELSE IF CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) > 1
RETURN [dbo].[PROPERSTATEMENT](SUBSTRING(@input,1,CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) - 1) ,SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput))) + ' ' + DBO.PROPERCASE(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1)) + ' ' + [dbo].[PROPERSTATEMENT](SUBSTRING(@input,CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) + LEN(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1)),LEN(@input)),SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput)))
ELSE
RETURN @input
RETURN @input
END

GO


Used function in properstatement : proper

Code:

USE [database]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERCASE] Script Date: 08/03/2010 18:06:25 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROPERCASE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[PROPERCASE]
GO

USE [database]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERCASE] Script Date: 08/03/2010 18:06:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[PROPERCASE]
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END

--Character variable declarations
DECLARE @output varchar(8000)
--Integer variable declarations
DECLARE @ctr int, @len int, @found_at int
--Constant declarations
DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int

--Variable/Constant initializations
SET @ctr = 1
SET @len = LEN(@input)
SET @output = ''
SET @LOWER_CASE_a = 97
SET @LOWER_CASE_z = 122
SET @Delimiter = ' ,-'
SET @UPPER_CASE_A = 65
SET @UPPER_CASE_Z = 90

WHILE @ctr <= @len BEGIN --This loop will take care of reccuring white spaces WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
SET @ctr = @ctr + 1
END

IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
BEGIN
--Converting the first character to upper case
SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END

SET @ctr = @ctr + 1

WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
BEGIN
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
BEGIN
SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
END

END
RETURN @output
END

GO