Dynamic SQL or Table Variables?

Hi all,
I was wondering if anyone knew all the pros and cons about using table
variables instead of dynamic SQL, and vice versa'
Problem i have, is writing a stored procedure which allows users to
search for particular records in the database. This is fine when all
the columns are nvarchar, but when it also involves Id fields, its a
pain.
Example
tblCompany
Id - int
Name - nvarchar
Status - nvarchar
SalespersonId - int
I would like users to be able to search on Name, Status, SalespersonId
For the string fields i use something like this.
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
But the user may or maynot have entered a SalespersonId. If they have
then i want to return records with that particular Id.
I was thinking of using the typical dynamic SQL approach as follows :
@.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
IF @.NAME IS NOT NULL
SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
IF @.STATUS IS NOT NULL
SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
IF @.SALESPERSONID IS NOT NULL
SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
Then i thought about using the newish table variables, so i would
define the table and then run the following.
' CREATE TABLE VARIABLE HERE
INSERT INTO TABLE_VARIABLE
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
IF @.SALESPERSONID IS NOT NULL
DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
What do you all think'Hi
http://www.sommarskog.se/dyn-search.html
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1163671811.306877.294670@.h54g2000cwb.googlegroups.com...
> Hi all,
> I was wondering if anyone knew all the pros and cons about using table
> variables instead of dynamic SQL, and vice versa'
> Problem i have, is writing a stored procedure which allows users to
> search for particular records in the database. This is fine when all
> the columns are nvarchar, but when it also involves Id fields, its a
> pain.
> Example
> tblCompany
> Id - int
> Name - nvarchar
> Status - nvarchar
> SalespersonId - int
> I would like users to be able to search on Name, Status, SalespersonId
> For the string fields i use something like this.
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> But the user may or maynot have entered a SalespersonId. If they have
> then i want to return records with that particular Id.
> I was thinking of using the typical dynamic SQL approach as follows :
> @.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
> IF @.NAME IS NOT NULL
> SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
> IF @.STATUS IS NOT NULL
> SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
> IF @.SALESPERSONID IS NOT NULL
> SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
> Then i thought about using the newish table variables, so i would
> define the table and then run the following.
> ' CREATE TABLE VARIABLE HERE
> INSERT INTO TABLE_VARIABLE
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> IF @.SALESPERSONID IS NOT NULL
> DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
> What do you all think'
>