p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Passing the Column name as the Parameter (http://p2p.wrox.com/showthread.php?t=48028)

rit01 September 19th, 2006 10:18 AM

Passing the Column name as the Parameter
 
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


All times are GMT -4. The time now is 11:33 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.