Find column name containg string 4

[tc3596]

I need to find a column name in a table. Here's what I have so far, but it obviously does not work.

(Select Name from syscolumns where name like 'end_bal_' + @Prd)

..@Prd is pre-set to 6 (which is the current period of the year)

This returns the column name (end_bal_6), which is perfect.
My problem is that I can't figure how to retieve those values from the table...

Select (Select Name from syscolumns where name like 'end_bal_' + @Prd)
From tbl

All I get is end_bal_6, (repeated over and over again). What I am doing wrong?

 

[JamesLean]

OK, for a start it is better to query the INFORMATION_SCHEMA views rather than sys tables directly:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'tbl'
  AND column_name LIKE 'end_bal_' + @prd

On a side note, I don't see why you need to do this select. All you're returning is the value 'end_bal_' + @prd - why do you need to then query the table for this?

Anyway, once you have this value you can use dynamic SQL to set up the query:

DECLARE @col_name varchar(20)

SET @col_name = 'end_bal_' + @prd

EXEC('SELECT ' + @col_name + ' FROM tbl')

--James

 

[tc3596]

Well,
That worked. Thanks. I haven't really used dynamic sql that much. It's never my first approach. In the future, I will try this approach more often when I get stumped.

Thnaks again.

 

[snag56]

I believe you have 2 choices:
(1)
DECLARE @mycol int
SET @mycol = 5
SELECT TOP 20 CASE @mycol
WHEN 1 THEN Col001
WHEN 2 THEN Col002
WHEN 3 THEN Col003
WHEN 4 THEN Col004
ELSE Col005
END AS mycol
FROM mytable

OR

(2)
DECLARE @mycol int
SET @mycol = 5
DECLARE @sql varchar(255)
SET @sql = 'SELECT TOP 20 Col00' + CONVERT(varchar,@mycol) + ' AS col FROM mytable'
EXEC(@sql)

The second example uses dynamic SQL.
For better performance I try to avoid dynamic SQL,
but it does have the advantage of being a little more flexible.
In your case, it depends upon how many variations of end_bal_* column exists.

 

[JamesLean]

Try not to go too overboard with dynamic SQL if you can help it, especially in stored procs. It loses a lot of the advantages of SPs, eg security, pre-compilation etc.

Remember you can use variables in place of expressions like:

SELECT * FROM tbl
WHERE col = @var

It's just when you come to replacing object names (and other things) that you run into trouble, as in your situation.

--James

 

[tc3596]

Can you do an Exec(sql...) inside am Exec(sql...) statement?

 

[JamesLean]

Technically you can, but I don't really see why you would want to - your code would get very messy for a start!

--James

 

[TysonLPrice]

This will find column names in an entire database:

SELECT a.name AS tablename, b.name AS columnname, c.name AS col_type_name, b.length AS Length, b.prec AS Prec, b.scale AS Scale,
c.xtype AS col_type_id
FROM dbo.sysobjects a INNER JOIN
dbo.syscolumns b ON a.id = b.id INNER JOIN
dbo.systypes c ON b.xtype = c.xtype
where b.name = 'Column name here'

 

[TysonLPrice]

This stored procedure will find strings in a database. The input parameter is the string. For example:

exec SearchAllTables FindMe

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END

GO

 

[tc3596]

Thank you for your posts. Here's the situation. My table looks like this...

BegBal_1 DrAmt_1 CrAmt_1 PrdAmt_1 EndBal_1 BegBal_2 EndBal_2...etc...

These are my colums which represent periods of a year. The rows are accounts that we have with totals for each period. Via another table, my procedure goes and finds the current period, then must try and match up this with a column name.

@DrAmt = (Select name From Inf_Schema Where name like 'DrAmt_' + @Prd)
@CrAmt = ...same for other columns

Exec('Update tbl
Set DrAmt_' + @Prd + '=' + @DrAmount + ','
CrAmt_' + @Prd + '=' + @CrAmount + ','
PrdAmt_' + @Prd + '=' + @DrAmount '+' + @CrAmount + ','
)

@DrAmount (is a total that comes from a cursor)
@CrAmount (same)


I can't get EndBal_1, which should be PrdAmt_1 + BegBal_1. BegBal_1 is what the period started with.