Column name as variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column name as variable

Author

Topic

dhoom
Starting Member

9 Posts

Posted - 2008-11-06 : 04:33:30

Hi,

I am trying to pass the column name as a variable into my sproc. But it doesnt seem to be giving the right result. Below is my sproc

CREATE PROCEDURE [dbo].[procAllProducts]
@SDate datetime,
@EDate datetime,
@Criteria nvarchar(100),
@ColumnName nvarchar(100)
AS

BEGIN

SELECT Product.ProductID as [Product ID], Product.Category as [Company Name], Product.SubCategory as [Product Name], Product.UnitMeasurement as [Package Size], Cast(ProductPriceHistory.UnitPrice as Numeric(10,2)) As [Unit Price],
CONVERT(varchar, ProductPriceHistory.StartDate, 101)as [Invoice Date]
FROM Product INNER JOIN
ProductPriceHistory ON Product.ProductID = ProductPriceHistory.ProductID
WHERE (ProductPriceHistory.StartDate BETWEEN @SDate AND @EDate) AND (@ColumnName LIKE '%' + @Criteria + '%') AND
(ProductPriceHistory.EndDate = '12/31/9999')
ORDER BY Product.ProductID

END

GO

Pls help!

Thanks,
~Dhoom.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-06 : 04:40:47

You almost certainly don't want to be doing this!

If you *need* to do things this way then you will have to resort to Dynamic SQL.

Please follow this link : http://www.sommarskog.se/dynamic_sql.html

To find out most everything there is to know about this topic. Also the reasons why and why not to go the dynamic route.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-06 : 04:46:07

In this case, you could consider have a set of IF statements for each potential column name. Then duplicate your SELECT statement for each possible permitation of possible columns (this assumes that you have a sensible schema that does not have columns added and removed in some crazy dynamic way) Something like

IF @columnName = 'employee' BEGIN

    SELECT x,y,z FROM table WHERE employee IN (a,b,c)

END
IF @columnName = 'staff' BEGIN
...
....

This might seem against good procedural programming practice (where you get 1 object and optimise the hell out of it and then use that exclusively but that approach doesn't work well on the database. If you make a dynamic query that can check multiple columns then you will have a very hard time getting good performance out of it (because it won't be able to work out which index's to use when you change columns)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dhoom
Starting Member

9 Posts

Posted - 2008-11-06 : 12:06:28

Ya I guess I can use the if statement since I am going to check only 2 columns. I guess that should work then.

Thanks,
~Dhoom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources