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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy