Advanced Stored procedure...

Hello,

(Excuse my english, i'm a french man)

I want to make a stored procedure on that model :

CREATE Procedure SGCP_GetEleveArgs
(
@.Arg1 nVarChar,
@.Val1 nVarChar
)
AS
SELECT *
FROM Eleve
WHERE @.Arg1 = @.Val1

So you understand that i want to pass my argument (@.Arg1) as a field of a column.
but it gives :

WHERE 'Nom' = 'Dupont'

but i want to have :

WHERE Nom = Dupont

for it to work.
Have you an idea to solve that problem ?

ThanxYou will want to use the EXEC function in SQL:

EXEC('select * from someTable WHERE ' + @.arg1 + ' = ''' + @.Val1 + '''')

I have not written SQL for a long time so the quote inside string part might be wrong.|||Exact :) ,

it seems to be the only one solution...

Thanx a lot

______________
Mik|||You could autogenerate the procs. For example, if you've got a table with the cols you could create

MyProcTableName1 @.Arg2
MyProcTableName2 @.Arg2
MyProcTableName3 @.Arg2

Then the client simply appends the first arg to the name...
CommandText = "MyProc" + TableName + strArg1

You get the idea...|||After research,

i found that we lose the advantages of stored procedure using EXEC because we don't use most important with stored procedure : precompilation of the command.

So I will not make like that

thanx|||Take a look at sp_executesql. This will used a compiled execution plan in some cases. There's really not any other dynamic way other than creating a dynamic SQL statement. Depending on the number of fields that could be searched, you could just put in several similar queries and use the appropriate one based on the field name. Could be cumbersome, but not bad if it's only a few.

if @.arg1 = 'field1'
select ... from table1 where field1 = @.arg2
if @.arg1 = 'field2'
select ... from table1 where field2 = @.arg2

You could also use a CASE statement:

Select ...
from table1
where
case @.arg1
 when 'field1' then field1
 when 'field2' then field2
 end = @.arg2

Might work. One problem is that if your stored procedure compiles an execution plan, it may use the wrong one. For example, the first time you run it, you search on field1. If you run it again, it may use the execution plan optimized for field1, but you're searching field2. You may get just as good results using dynamic SQL. It will create a new execution plan each time, but one that's optimized for the current query, not the last one.

Bon chance! (Only 5 years of French classes a LONG time ago.)