Variable tables in a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Variable tables in a string

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-27 : 15:50:27

Is it possible to have a variable table in an sql string to be called by exec?

I have all the results I need in @Table but the final phase is to filter using parameters passed by user so I am builing the select statement dynamically to return the results to the user.

I have it like this:

SQLString = 'SELECT f1, f2, f3, f4 FROM @Table WHERE site = '' + @Site + '''

IF <condition>
SELECT SQLString = SQLSTRING + ' AND f1 = ' + @F1 + '

IF <condition>
SELECT SQLString = SQLSTRING + ' AND f2 = ' + @F2 + '

IF <condition>
SELECT SQLString = SQLSTRING + ' AND f3 = ' + @F3 + '

exec(SQLString)

Realise the quotes aren't right at the end, but am trying to get passed the error 'must declare variable @Table first'

Have tried taking @Table out and placing in quotes like @Site but still get same error?

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-27 : 17:14:51

I have this working now by using a temp table, but prefer to use variable tables so would be interested to know if this is possible :)

SQLServerSpotlight
Yak Posting Veteran

57 Posts

Posted - 2005-11-27 : 17:33:03

Unfortunately you can not use local table variable in dynamic SQL.
You can however put your delcare @t table in the dynamic sql itself.

This path leads to Massive dynamic sql statemtent though - so be careful.

Kristen
Test

22859 Posts

Posted - 2005-11-28 : 02:21:54

As SQLServerSpotlight said. But this sort of thing is better done with sp_ExecuteSQL than EXEC() anyway - better performance because the various "forms" of the query are cached.

However, what's wrong with:


SELECT f1, f2, f3, f4 
FROM @Table 
WHERE     site = @Site
      AND ((NOT <condition1>) OR (f1 = @F1))
      AND ((NOT <condition2>) OR (f1 = @F2))
      AND ((NOT <condition3>) OR (f1 = @F3))

(In essence you wouldn't write "NOT <condition1>" you'd turn it round. So if <condition1> is "@F1_TEST = 1" then you would write:

AND ((NOT (@F1_TEST = 1)) OR (f1 = @F1))

and simplify that to:

AND (@F1_TEST <> 1 OR (f1 = @F1))

Kristen

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-28 : 16:40:38

Thanks chaps, once again I need to ponder your suggestions/code and see what occurs :)

I think I like you way Kristen as it appears to be neater, but at fisrt glance I can't make head nor tail of it......but I will :)

Ta

Kristen
Test

22859 Posts

Posted - 2005-11-28 : 21:19:52

If you provide some real examples for "IF <condition>" - or, indeed, the full code for a query, I can probably give you a better example by way of explanation

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources