SQL procedure parameters

[Maziar Aflatoun]

Hi everyone,

I need to create a procedure that would take the two parameters @FieldName
and @FieldValue and replaces it in my SQL statement without using Exec
because Exec can take at most 128 characters.

CREATE PROCEDURE Get_Person_By_Field
@FieldName varchar(20),
@FieldValue varchar(100)
AS
..
..
..

INSERT INTO
#TempPerson(PID,Title,PhNum1,PhExt1,PrimaryEmail,F ullName,CompanyName

) SELECT PID,Title, PhNum1, PhExt1,PrimaryEmail,FullName, CompanyName FROM
Person LEFT JOIN Company ON Person.CID = Company.CID WHERE
@FieldName='@FieldValue'

Thank you
Maz.

[Erland Sommarskog]

Maziar Aflatoun (ma***@rogers.com) writes:

I need to create a procedure that would take the two parameters @FieldName
and @FieldValue and replaces it in my SQL statement without using Exec
because Exec can take at most 128 characters.

CREATE PROCEDURE Get_Person_By_Field
@FieldName varchar(20),
@FieldValue varchar(100)
AS
.
.
.
INSERT INTO
#TempPerson(PID,Title,PhNum1,PhExt1,PrimaryEmail,F ullName,CompanyName

)
SELECT PID,Title, PhNum1, PhExt1,PrimaryEmail,FullName, CompanyName FROM
Person LEFT JOIN Company ON Person.CID = Company.CID WHERE
@FieldName='@FieldValue'

If you are doing things like this, chances are good that you have a
flawed designed.

In any case, this is possible to achieve with sp_executesql, or EXEC() for
that matter. I don't know what you mean with 128 characters. Possibly you
are doing the mistake of saying

EXEC @sql

Here you @sql cannot be longer than 128 chars, unless there are periods
in the string, as @sql here should be the name of a stored procedure. The
correct syntax for dynamic SQL is EXEC(@sql). But sp_executesql is better,
see http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp