Dynamic SELECT statement with SP by querying information_schema.columns in SQL Server 2008/2005

Some days from now, I am getting new requests everyday to solve and I love to solve problems as much as I can and help community more and more.  Situation was like, there is a table which has few fields like Name, Address, Age ,_NO etc. but reader don’t want to return columns starts with “_”.  Very simple thing, right. Suppose name of Table is “DemoTable” you can simply write down
SELECT Name, Address, Age from DemoTable
But requirement doesn’t ends here,  it needs to be dynamic and should work with any table without even specifying the column name. Here we have to start thinking something different, let me show you how I did that? Have a look at below script and enjoy SQL Programming!!!!
–Demo table
create table DemoTable
(
Name varchar(10),
Address varchar(10),
Age int,
_No int
)

 
–SP which will accept tablename and character of the column name
–which needs to be eliminated from the list of results.
create proc DynamicSelect
@TableName varchar(20),
@FilterChar char(1)
AS
BEGIN
SET NOCOUNT ON
      DECLARE @Cols NVARCHAR(500)
      SET @Cols=
      –gathering column list in @Cols variable by querying Information_Scehma.columns table
      SELECT @Cols=@Cols+ s.column_name + ‘, ‘ FROM
      (select COLUMN_NAME from  information_schema.columns where TABLE_NAME=‘TableA’ and charindex(@FilterChar,COLUMN_NAME)<=0) AS s
      SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
      set @cols=‘SELECT ‘ + @Cols + ‘ From ‘ + @TableName
      EXECUTE sp_executeSQL @Cols
END


–check SP whether it actually works!!!!
Exec DynamicSelect ‘DemoTable’,‘_’
 
Actually above task could be done by CURSOR also but as long as possible I always would like to avoid the cursor.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: