Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
billieh
Starting Member
2 Posts |
 Posted - 2010-09-21 : 18:43:07
|
| I am looking to create a Query that will produce results from a table based on the variables sent to it. Basically, I want to be able to use the same query, but populate just the 4 variables, so no one has to edit the actual query itself. I would like to set the variables, Show the 'before', make the change, then show the 'After' to verify it works. The example, which doesn't work, because select can't seem to pull variables the way this is written: DECLARE @DBName VARCHAR(50)DECLARE @TableName VARCHAR(50)DECLARE @ColumnName VARCHAR(50)DECLARE @BeforeString VARCHAR(50)DECLARE @AfterString VARCHAR(50)DECLARE @RSLT VARCHAR(100)Set @DBName = 'MyAddyBook'Set @TableName = 'AllUsers'Set @ColumnName = 'DEFAULT_EMAIL'Set @BeforeString = '123456[at]test.com'Set @AfterString = '654321[at]test.com'Use @DBNameSelect @ColumnName from @TableName AS 'Before Change'Where @ColumnName like '@BeforeString'Update @TableNameSet @ColumnName = Replace(@ColumnName, @BeforeString, @AfterString) -- this is used because there may be other comma separated information in this column. Use @DBNameSelect @ColumnName from @TableName AS 'After Change'Where @ColumnName like '@AfterString' and date(edit_date)>=date( NOW())Results: Before Change------------------------123456[at]test.com1 Row(s) Returns**Query Executed Successfully. **After Change------------------------654321[at]test.com1 Row(s) ReturnsSecondly: Once I get this part working, I would like to have this script loop through multiple DBs on the same Server. I am guessing that there would have to be a cursor or loop of some sort to specify what the 'Use [DBNAME]' changes to... Any help would be appreciated on these topics. Thank you so much. Google hasn't been very helpful explaining why variables can't pass in a select query.B H |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
billieh
Starting Member
2 Posts |
|
|
|
|
|
|
|
How to Query a Table using Defined @Variable?