@VarName as Table Name in SQL

[astonmak]

Hi all,

Does it allow us to put @Variable as table name in SQL Statemnt, cause I want to build a Stored Proc allow me to call any table by the parameter, but I meet a error.

SELECT fld1 FROM [@lcTableName]

Thanks for your help
Aston

 

[SonOfEmidec1100]

There a many threads on this topic, basically you do the this -

DECLARE @CMD VARCHAR (500)

SET @CMD = 'SELECT fld1 FROM [' + @lcTableName + ']'

EXEC (@cmd)

 

[clarkin]

If you want to find out more about the technique, its called 'Dynamic SQL' (useful to search for it).

Also it can be problematic with users and their permissions, or so I've heard: as in you can grant a DB user permission to run a certain stored proc, but if it contains UPDATE/DELETE or the like inside dynamic SQL they may not be able to run the procedure.

 

[Erics44]

Hi
I have a question on dynamic sql

I need to add a string parameter to dynamic sql but I am unsure how to.

I have

select @sql = @sql + 'WHERE [Market]= ' + @Market + ' '

this doesnt work because to run the sql I would need quotes round the @Market.

How do I put quotes in the dynamic sql?
Thanks

 

[JohnDTampaBay]

See your other thread thread183-1025630

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)