Passing the Column name as the Parameter

rit01 · September 19th, 2006, 10:18 AM

Hi All

I have been scouring the net this afternoon trying to find out how to pass in the column name as a parameter within my sproc. It sounds as though it is bad practice but I cannot think of another way to get the same results.

This is what I was attempting:

SELECT DISTINCT @columnname From tblTable

Does anyone have any other ways to acheive the same objective?

Many thanks

Rit

dparsons · September 19th, 2006, 01:12 PM

You can't create a SELECT list from variables. (By that I mean you can't dynamically select a column from a table using variables) What exactly are you trying to do?

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

rit01 · September 19th, 2006, 03:17 PM

Hi

What I am trying to acheive is enable the user from my asp.net application to be able select a disinct list from any column within my table.

I suppose I could create this procedure by using a CASE statment that reads a Parameter value which then decides which SELECT DISTINCT query to use.

Many thanks for you time.

Rit

rit01 · September 19th, 2006, 03:20 PM

... as in each SELECT DISTINCT query would be using a different column and the parameter being the value which is used to decide which query to run.

Can be a little vague sometimes.. sorry!

dparsons · September 19th, 2006, 03:52 PM

You will have to forgive me, its been a long day i misspoke about the SELECT List;

You should be able to do something like this:

Declare @column varchar(255)
Declare @select varchar(255)

Set @select = 'SELECT ' + @column + ' FROM table'
exec(@select)

which should execute the select perfectly.

(If your queries are going to get complex (e.g. over 255 characters) you may want to use an Nvarchar field)
--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

rit01 · September 19th, 2006, 04:00 PM

Thank for the guidance dparsons. I'll give it a go.

Rit

rit01 · September 20th, 2006, 06:29 AM

Hi

I seem to get the following error when applying this method...

Syntax error converting the varchar value.. SPROC.. ..)to a column of data typ... doesn't actually tell me what datatype. Here is a copy of my sproc can you or anyone see where I could be going wrong?...

Code:

CREATE PROCEDURE dbo.Mysproc

    @param1        INT = NULL,
    @param2        INT = NULL,
    @param3        INT = NULL,
    @myColumn     varchar(50)

AS

Declare @cmd    nvarchar(4000)

SET @cmd = 'SELECT ' +@myColumn+ ' As FilterDDL
FROM myTable1 AS A

INNER JOIN     myTable2 AS B ON B.an_ID = A.an_ID
INNER JOIN     myTable3 AS C ON B.anotherID = C.anotherID
INNER JOIN     myTable4 AS D ON B.yetanotherID = D.yetanotherID

WHERE     D.yetanotherID = COALESCE('+@param1+', D.yetanotherID)
AND        C.anotherID = COALESCE('+@param2+', C.anotherID)
AND        B.an_ID = COALESCE('+@param3+', B.an_IDD)'

EXEC(@cmd)
GO

Many thanks in advance

Rit