Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Paramater Concatenation Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 2:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:48 PM
Points: 72, Visits: 204
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.
Post #1501348
Posted Thursday, October 3, 2013 2:54 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 597, Visits: 7,165
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.




-
Post #1501351
Posted Thursday, October 3, 2013 4:23 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 646, Visits: 2,992
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 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)...


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1501373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse