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 12»»

Store procedure variable Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 8:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 312, Visits: 590
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'
Post #1415869
Posted Tuesday, February 5, 2013 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
what error?

declare @mypath varchar(50), @sql nvarchar(100)
set @mypath = 'myserver.mydatabase.dbo.'

set @sql = 'select * from ' + @mypath + 'order'

sp_executesql @sql




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1415874
Posted Tuesday, February 5, 2013 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
adonetok (2/5/2013)
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'


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1415881
Posted Tuesday, February 5, 2013 8:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 312, Visits: 590
Error is:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '+'.

But if I run your script (ad @sql), it is ok

Post #1415887
Posted Tuesday, February 5, 2013 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1415890
Posted Tuesday, February 5, 2013 8:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 312, Visits: 590
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

Post #1415897
Posted Tuesday, February 5, 2013 8:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1415901
Posted Tuesday, February 5, 2013 8:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:52 AM
Points: 449, Visits: 1,370
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?
Post #1415903
Posted Tuesday, February 5, 2013 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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/




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1415914
Posted Tuesday, February 5, 2013 8:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 312, Visits: 590
If serverA is down, user can switch to backup serverB.
The store procedure is store in serverA
Post #1415917
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse