Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 19th, 2006, 10:18 AM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default 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
__________________
Rit
www.designandonline.co.uk
INSPIRE | CREATE | DELIVER
Reply With Quote
  #2 (permalink)  
Old September 19th, 2006, 01:12 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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.
Reply With Quote
  #3 (permalink)  
Old September 19th, 2006, 03:17 PM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old September 19th, 2006, 03:20 PM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

... 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!
Reply With Quote
  #5 (permalink)  
Old September 19th, 2006, 03:52 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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.
Reply With Quote
  #6 (permalink)  
Old September 19th, 2006, 04:00 PM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

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

Rit
Reply With Quote
  #7 (permalink)  
Old September 20th, 2006, 06:29 AM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to passing multi parameter diablo Crystal Reports 0 May 26th, 2008 06:13 AM
passing parameter sarah lee ASP.NET 1.0 and 1.1 Basics 3 September 5th, 2006 04:29 PM
passing parameter.. suzila VB.NET 2002/2003 Basics 7 May 17th, 2004 08:28 PM
Parameter passing psambor Beginning PHP 0 September 17th, 2003 01:47 AM
Passing Parameter tgopal Javascript 3 August 7th, 2003 07:54 PM



All times are GMT -4. The time now is 11:27 PM.


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