October 3, 2013 at 2:43 pm
Hi Everyone,
DECLARE @CN VARCHAR (100)
SET @CN = 'abc pvt Ltd'
select * from [@CN'+'$Production]
My output should be select * from [abc pvt Ltd$Production].I DO NOT want to use a string for my Select Query
Could anyone of you help on me this.
October 3, 2013 at 2:54 pm
As far as I know of, without building your query as a string and passing it to EXEC or (preferably) sp_executesql, this isn't possible, to the best of my knowledge.
If I remember correctly, it has something to do with requiring database names in queries to be explicit declarations; you can accomplish this with a string that gets run through EXEC, since the variable is replaced by its value, but not in a non-EXEC query.
- 😀
October 3, 2013 at 4:23 pm
You can include table names or derived tables in your from clause but not a non-static value. Dynamic SQL (using sp_executesql not EXEC) is the way to go for this.There is no problem with Dynamic SQL when it done correctly (e.g. in a way where SQL Injection can't happen).
That said, here is an alternative solution if you MUST avoid Dynamic SQL.
--sample data
IF OBJECT_ID('tempdb..#tbl1') IS NOT NULL DROP TABLE #tbl1;
IF OBJECT_ID('tempdb..#tbl2') IS NOT NULL DROP TABLE #tbl2;
IF OBJECT_ID('tempdb..#tbl3') IS NOT NULL DROP TABLE #tbl3;
IF OBJECT_ID('tempdb..#tbls') IS NOT NULL DROP TABLE #tbls;
SELECT 't1' AS tbl INTO #tbl1;SELECT 't2' AS tbl INTO #tbl2;SELECT 't3' AS tbl INTO #tbl3;
-- catch-all query
WITH tbls(tbl) AS (SELECT '#tbl1' UNION SELECT '#tbl2' UNION SELECT '#tbl3')
SELECT * INTO #tbls FROM tbls
GO
DECLARE @CN VARCHAR (100)='#tbl1';
IF EXISTS (SELECT tbl FROM #tbls WHERE tbl=@CN)
BEGIN
IF @CN='#tbl1'
SELECT * FROM #tbl1
--SELECT TOP 10 * FROM #tbl1
IF @CN='#tbl2'
SELECT * FROM #tbl2
--SELECT TOP 10 * FROM #tbl2
IF @CN='#tbl3'
--SELECT TOP 10 * FROM #tbl3
SELECT * FROM #tbl3
END
ELSE
SELECT 'table does not exist or has not been entered into #tbls. Fire someone.' AS Msg
Here I am using a catch-all query; see this article[/url] by Gail Shaw for more information about catch-all queries...
One final important note
Do you really need all the rows from any table defined by @CN? Even if it has a couple billion rows?
I would suggest that you have the query return a limited number of values (note my commented out code above)...
-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply