Just a quick SP question.... - 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 2000 Forums

SQL Server Development (2000)

Just a quick SP question....

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-20 : 05:27:54

Hi There,

I have the following scenario:

I have a list box with 3 options. The option selected defines which column in the database is compared against a variable value.

So for example, if the list box values were:

Column1
Column2
Column3

...then the code could be something like this:


If listbox = "Column1" then
SQL = SELECT * FROM table WHERE Column1 = "& variable &"

   else if listbox = "Column2" then
   SQL = SELECT * FROM table WHERE Column2 = "& variable &"

      else
      SQL = SELECT * FROM table WHERE Column3 = "& variable &"

   end if
end if

I would like to recreate this process in a Stored Procedure in SQL Server (2000). Can I do this in a single SP? (because I don't want to have hundreds of stored procedures!!)

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 05:33:30

Yes, you can..you will have to use dynamic sql like this:

Declare @SQL varchar(8000)

If @param1 = 'Column1'
Set @SQL = 'select .... where column1 = ' + @param2
else if @param1 = 'Column2'
Set @SQL = 'select .... where column2 = ' + @param2
...

exec(@SQL)

Harsh Athalye
India.
"Nothing is Impossible"

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-20 : 05:38:22

quote:
Originally posted by harsh_athalye

Yes, you can..you will have to use dynamic sql like this:

Declare @SQL varchar(8000)

If @param1 = 'Column1'
Set @SQL = 'select .... where column1 = ' + @param2
else if @param1 = 'Column2'
Set @SQL = 'select .... where column2 = ' + @param2
...

exec(@SQL)

Harsh Athalye
India.
"Nothing is Impossible"

Great, thanks.....I was just a little unsure about the full capabilities of SP's....cheers :)

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-20 : 05:44:37

Would it be possible to actually pass the name of the column in a variable and use it in the statement like this......

@ColumnName
@Param

SELECT * FROM table WHERE "& ColumnName &" = "& Param &"

?

(not quite sure of the syntax in the SP....so I've just used ASP-type code.....haha)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 05:45:04

You can also try this without need to use dynamic sql..I haven't tested it...but you can check it:

select * from table
where 1 = (case when @param1 = 'Column1' and column1 = @param2 then 1
                       when @param1 = 'Column2' and column2 = @param2 then 1
                        ....
		else 0
               end)

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources