Parameters 1

[tsunami100]

Hi,

Does anybody know how i can use a fieldnaam in an sql-statement as a parameter.

Select Field1 ,Field2 From Database where param1 = param2

IMPORTANT is that i'm trying to do this in the querybuilder from VB.Net so i can not use any strings
The user can choose Field1 or Field2 as param1
Is this possible ?
The statement above doens't work but gives you an idea what i mean.


I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[mrdenny]

In order to do this you will need to use dynamic SQL. You can't have a variable for a field name.

declare @CMD varchar(1000)
declare @Param1 varchar(100)
declare @Param2 varchar(100)
set @cmd = 'select Field1, Field2 from {TableName} where ' + @Param1 + ' = ''' + @Param2 + ''''
exec (@cmd)

Your other option would be to use a stored procedure, which would remove the need to use the dynamic SQL.

create procedure usp_demo1
   @Field varchar(100),
   @Param varchar(10)
as
if @Field = 'field1'
   select field1, field2
   from {TableName}
   where field1 = @Param
if @Field = 'field2'
   select field1, field2
   from {TableName}
   where field2 = @Param
go
exec usp_demo1 @Field='field1', @Param='test'

As you can see with the proc there is no dynamic SQL, but you have to update the proc to add additional fields into the where clause.

But the proc will be faster and easer to execute than the dynamic SQL. You will also have less permissions issues with the proc.

I'm sorry to hear about your friend that you lost.

Let me know if my code does or doesn't point you in the correct direction.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[tsunami100]

Hi mrDenny,

Thank you so much for your reply.
I'm sorry but i forgot to mention that i'm using an Access-database here.
Isn't it only possible to write stored procedures with SQL-server.
Maybe you can help me out.
Thanks again, i really appreciate this.

P.S. A star is something you deserved already!


I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.