February 5, 2013 at 8:06 am
Below is statement for a table
select * from myserver.mydatabase.dbo.order
I want to declare a path to modify above statement but got an error.
declare @mypath varchar(50)
set @mypath = 'myserver.mydatabase.dbo.'
select * from @mypath + 'order'
February 5, 2013 at 8:22 am
adonetok (2/5/2013)
Below is statement for a tableselect * from myserver.mydatabase.dbo.order
I want to declare a path to modify above statement but got an error.
declare @mypath varchar(50)
set @mypath = 'myserver.mydatabase.dbo.'
select * from @mypath + 'order'
You have to use dynamic sql for this. You will also have to wrap your table name in [] because you are using reserved words as your object name.
declare @mypath varchar(50)
set @mypath = 'myserver.mydatabase.dbo.'
declare @sql nvarchar(max)
set @sql = 'select * from ' + @mypath + '[order]'
exec sp_executesql @sql
I have a feeling this is the beginning of a generic stored procedure that will receive the table name as a parameter. This type of dynamic querying is a tell tale sign of that. This is not a good approach to stored procs. It will cause nothing but issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 8:28 am
Because the query is dynamic, and without sp_executesql it will not expand the contents of the variable.
Dynamic queries like this are bad practice, I would highly recommend not doing this.
February 5, 2013 at 8:37 am
The purpose to use it is that I need to create a store procedure to read one Order table. Based on server status, sometime user need to change server name from application. The store procedure should be something like below so that user can switch server name by passing parameter @dailytablepath
CREATE PROCEDURE InitList
@dailytablepath varchar(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM @dailytablepath + '[ORDER]'
END
February 5, 2013 at 8:39 am
The code I posted will accomplish this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 8:43 am
Unless i'm missing the point??
The stored procedure exists on the server. The application connects to that server. If you need to change the server in the application then the stored procedure will be called based upon that connection meaning you would surely only need
SELECT * FROM dbo.order
or
SELECT * FROM mydatabase.dbo.order
Is there any need to pass in the server name and do this dynamically as each server holding the orders table would have the SP?
February 5, 2013 at 8:53 am
michael.higgins (2/5/2013)
Unless i'm missing the point??The stored procedure exists on the server. The application connects to that server. If you need to change the server in the application then the stored procedure will be called based upon that connection meaning you would surely only need
SELECT * FROM dbo.order
or
SELECT * FROM mydatabase.dbo.order
Is there any need to pass in the server name and do this dynamically as each server holding the orders table would have the SP?
I guess this is because they don't want to play about with the connection strings, so instead they are using linked servers over to other servers. But as the user needs to pass in the server, the database the schema, why bother with dynamic SQL and just do it with a dynamic selection of the right connection string based on the users input/
February 5, 2013 at 8:55 am
If serverA is down, user can switch to backup serverB.
The store procedure is store in serverA
February 5, 2013 at 8:57 am
If the proc is in A and A is down, then they wont be able to run the proc as they cant get to A, so your logic is flawed.
What high availability routines do you have in place?
February 5, 2013 at 9:01 am
Sorry, it is my fault. I did not make clear.
What I mean is [Order] table in serverA is not correct so that user need to use [Order] table in serverB.
February 5, 2013 at 9:06 am
Well then as long as you have a linked server from ServerA to ServerB you will be ok.
But I seriously would recommend changing the connection string and not using dynamic SQL.
February 5, 2013 at 9:21 am
anthony.green (2/5/2013)
Well then as long as you have a linked server from ServerA to ServerB you will be ok.But I seriously would recommend changing the connection string and not using dynamic SQL.
+1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 1:54 pm
Is dynamic SQL bad in general or just when it opens up this sort of a security hole allowing for the SQL to fully specified or at least the table?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply