Externalizing TSQL Code
-
Newbie here...Code from within QueryAnalyzer
declare @sql nvarchar(100)
declare @CDM nvarchar(1)
set @sql = (select Q.SqlDesc from SgSQL Q where RTrim(Q.SQLName) = 'Test')
set @CDM = 'A'
Exec(@SQL)
The "set @sql" statement retrieves...Select @CDM as Test, C.CorpID from corp C where C.corpid = 1
When I run this test snippit I get an error message stating that @CDM has not been declared. How can I achieve my goal...have an external table of select statements that include variables...these statements would be pulled in and run as needed.
THanks! cdm
-
Can you post the exact code you are actually using?
-
I thought I replied to this but it didn't show up...Sorry...I'll try again.
I can't provide the actual code...I haven't built the code yet. Since this part of the project is still in the planning stage, I'm only working with test snippits of code so I can gradually build it up. I need to get over the externalizing of the TSQL code though to make it work like we want.
The project calls for monitoring and FTP site for a tab delimited file that arrives every morning. We import this data, clean it up and then update or append our SQL tables. The final step is to create record batches (reports). The data contains multiple records from many stores with multiple records per store as normal.
Our challenge is to create multiple data "batches" (translate multiple select statements) for each store. These batches can be turned On/Off for each store for any day. THe bottom line is that Any Store, can elect to have any Report, For any set of days.
My planned structure is: Store table <-->> StoreReport table <<--> ReportSQL table
Where the StoreReport table contains: StoreID, ReportSQLID, and Schedule (MTF) (Mondays, tuesdays, Fridays)
So when we run our logic, we grab the store we want to process, process through the StoreReport table to get all the reports valid for that day and grab the SQL code as we need it.
Like I said, I'm just starting this final project step and am trying to run code that is pulled from a separate table. As long as the code doesn't contain variables, I'm fine. But the moment I include a variable it gives me the problem...It seems that the variable appears out of scope or something.
Does this help? THanks! cdm
-
It's more clear now. I think that the simplest way to apporach this problem would be to have the date of the data inserted in the table. Then select based on that. It's gonna be much easier than any dynamix sql you can come up with.
-
You can't create a variable in the batch and have it be accessible in the dynamically executed SQL. The variable has to be part of the dynamic SQL.
When you run dynamic SQL like this, it essentially runs in it's own space (think seperate window in Query Analyzer) and won't have access to anything that's not global in the batch that executes it.
(Wow, way too early, and that's probably confusing)
Greg Walker
DBA, ExpenseWatch.com -
No it's crystal clear... but I still think that the design is faulty at the moment...
-
I understand now about the Exec running in its own space. I plan to read up on it a bit to see if I can get more details...as to the design...I'm continuing to look at different ways to do the job and I appreciate all comments in this reguard.
Thanks for your help folks! cdm
-
Maybe you should post more details about how the data must be moved so we have a better idea of the task at hand.
-
Thanks for the Exec sp... statement, I'll try it out.
As for more details...I'll try...it is conceptually simple...
Background...Tables: Import (incoming data), Stores (storeids and desc), BatchesSQL (batchid, desc, sql code), BatchesSQLStore (storeid, batchid, schedule...links the store and BatchesSQL tables, and maintains the days in the schedule column that this report is valid for the given store id.
Processing...I need to create multiple data subsets (batches) out of the Import table based on the StoreID, Batch Type (different selects) and a schedule.
I create a list of stores I need to process by doing a select distinct on the storeID out of the Import table into a cursor (so I can process them one at a time).
I grab the first store id and then grab all batchid's (out of the BatchesSQLStore table) assigned to that store that are valid for today (into a nested cursor, so I can process of THEM in turn)
Once I have a list of batches, I pull the first batchesSQLStore record, observe the BatchID, and get the record out of BatchesSQL with the same ID. I copy the SQL code found their into an @sql variable and try to execute it. Once done, I loop to the next batchid and so on until all batches for that day for that store are finished.
WHen all batches are done, I bounce back to the outer cursor loop, grab the next store and start it all over again...a couple hundred times.
Thats the construct...my problem arose when I tried to run the Exec(@SQL) statement when @sql contained variables in the SQL code.
I think I can work around the problem by removing all variables from the called code but I need to prove I can do it for everything...I'm still working on it...and I have yet to try the latest suggestion..executing the stored procedure.
THanks everyone! cdm
-
Thanks for helping everyone....based on the input and another thread regarding retrieving values from an executed procedure, that following code works just fine.
I'm not showing the variable declarations but I did create a table called NumberTable to hold a single value..
Here is the solution that works....
Set @WhereClause = (Select BQ.BatchSQL from BatchesSQL BQ where BQ.BatchesSQLID = @BatchesSQLID)
-- The following is the Get Sign Count Routine
Set @AisleBreaks = 0
Set @ExecSQLStr = N'(select count(distinct G_DeptAisle) From ItemLibraryImport' + Char(13)
Set @ExecSQLStr = @ExecSQLStr + N'Where RTrim(LotNumber) = @XCurrentStore '
Set @ExecSQLStr = @ExecSQLStr + @WhereClause + N')'
Set @ExecSQLPar = N'@XCurrentStore nvarchar(4)'
Truncate Table NumberTable
Insert Into NumberTable
Exec Sp_ExecuteSQL @ExecSQLStr, @ExecSQLPar, @XCurrentStore = @CurrentStore
Set @AisleBreaks = (select * from NumberTable)
....etc.
If you have a nicer way to do this...please thump it into me.
Frankly, I think that handling Dynamic SQL this way really sucks...One would think the M$ could come up with a cleaner way to do this....I now have to code a complete "Insert into Table" routine this way because it's Where Clause is dynanic too. But at least you've shown me that it can be done. Sigh
Thanks again! cdm
-
That's why it's better to have a simple normal design. But then again I don't know why you need to run externally set queries to insert the data so it's up you to you to figure it out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply