>Hello,
>
>I am writing a stored procedure where the tablename in a select statement can be variable.
>
>In VFP I probably would use name substitution:
>
>lcMyTable = "Users"
>lcUser = "Christian"
>SELECT * FROM (lcMyTable) WHERE Username = lcUser
>
>
>In SQL that approach gives me problems, it either says "Must declare the table variable" or if I used a dynamic SQL Must delcare the scalar variable". I guess there is a simple approach but I just can't figure out what is the right one.
In SQL Server you must use dynamic SQL for this purpose, e.g.
declare @SQL nvarchar(max)
set @SQL = 'select * from ' + quotename(@TableName) + ' where UserName = @cUser'
execute sp_ExecuteSQL @SQL, N'@cUser varchar(20)', @cUser
If it's not broken, fix it until it is.
My Blog