MS SQL Server - Dynamic SQL or Table Variables??

Author

Dynamic SQL or Table Variables??

Nemisis

2006-11-16, 5:21 am

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??

Uri Dimant

2006-11-16, 5:21 am

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??
>

Sponsored Links