 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Other SQL Server Topics / February 2008sql query which has got 3 dynamic parameters
I have a query which works fine when hardcoded which is as follows SELECT SchemaFields.SchemaID, SchemaFields.ObjectClass, SchemaFields.ObjectType, 'New' AS ActionType, DataQuery.Count FROM SchemaFields LEFT OUTER JOIN (SELECT [01-RecordClass], [02- RecordAction], COUNT(*) AS Count FROM TestData6061.dbo.accounting WHERE (AutomationType = 'Loader') AND ([Negative Testcase] = 0) GROUP BY [01-RecordClass], [02- RecordAction]) AS DataQuery ON DataQuery.[01-RecordClass] = SchemaFields.ObjectType AND DataQuery.[02-RecordAction] = 'New' WHERE (SchemaFields.SchemaID = 133) GROUP BY SchemaFields.SchemaID, SchemaFields.ObjectClass, SchemaFields.ObjectType, DataQuery.Count In the above query 'TestData6061', 'accounting' (from the 2nd select statement) and 'SchemaID' are all going to be dynamic. Is there anyway I can generalize this using functions/stored procedures etc > I have a query which works fine when hardcoded which is as follows > [quoted text clipped - 25 lines] > statement) and 'SchemaID' are all going to be dynamic. Is there anyway > I can generalize this using functions/stored procedures etc I am sorry there are not 3 dynamic parameters, but 'accounting' is the only parameter which is dynamic. accounting is one of the tables under TestData6061 and there are bunch of others so I want to have a query which will give me table names for 'TestData6061' in the above example > I am sorry there are not 3 dynamic parameters, but 'accounting' is the > only parameter which is dynamic. accounting is one of the tables under > TestData6061 and there are bunch of others so I want to have a query > which will give me table names for 'TestData6061' in the above example Generally, when there are many tables with the same schema which makes you long for being able to parameterise the table name, the general opinion is that there is a flaw in the design. A table is intended to describe a unique identity, and therefore all tables are expected to have a unique set of columns. And thus there would be little reason to parameterise table names - and therefore there is no direct feature to do this. If you give more details on why you have all these similar tables, we can give ideas on how to improve the design. Or, if you are stuck with it, what possible workarounds there may be.  SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > I am sorry there are not 3 dynamic parameters, but 'accounting' is the > > only parameter which is dynamic. accounting is one of the tables under [quoted text clipped - 18 lines] > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks Erland for the reply first of all. The thing is like I didn't really design the system. So here is what I came up with so far. I have a store procedure to which I pass a number which will give me a particular entry in that database. say if I type exec SP_FETCH_ROWS 1 its going to return accounting or if I pass 4 its going to return calendar etc set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER procedure [dbo].[SP_FETCH_ROWS](@num int) AS BEGIN SELECT Table_Name from TestData6061.INFORMATION_SCHEMA.Tables a where @num =(select count(*) from TestData6061.INFORMATION_SCHEMA.Tables b where a.Table_Name >= b.Table_Name) END So now I tried call this in a loop as below declare @Count int declare @MaxCount int set @Count = 0 set @MaxCount = 29 while @Count < @MaxCount begin Set @Count = @Count + 1 if @Count = 1 or @Count = 4 or @Count = 7 or @Count = 8 or @Count = 14 or @Count = 17 or @Count = 18 or @Count = 22 or @Count = 25 or @Count = 27 or @Count = 28 or @Count = 29 exec releases.dbo.SP_FETCH_ROWS @Count end which isn't working though. The above loop ideally will give me list of items at those specific positions in TestData6061.INFORMATION_SCHEMA.Tables *** The above loop works when I run it through 'New Query' on the sql server UI but when I open up one of the tables on the db and n then click on the 'show sql pan' and enter the same thing it only return 'accounting' *** Any idea how this could be fixed? > Thanks Erland for the reply first of all. The thing is like I didn't > really design the system. So here is what I came up with so far. And then you go on at length, without answering his question. Why does the system contain all these similar tables (e.g. A, B, C), instead of a single table with an extra column (whose value is e.g. A, B, C)? Note that you may want to include that extra column in one or more indexes, for speed. Do you have the ability to redesign the system? If not, then can you talk with someone who does? It's worth investigating whether the cost of such redesign would be repaid by making it easier to work with the system afterward. > Thanks Erland for the reply first of all. The thing is like I didn't > really design the system. So here is what I came up with so far. I > have a store procedure to which I pass a number which will give me a > particular entry in that database. say if I type exec SP_FETCH_ROWS 1 > its going to return accounting or if I pass 4 its going to return > calendar etc First: don't name your procedures sp_something, as the sp_ prefix is reserved for system procedures. Next, I don't understand what this SP_FETCH_ROWS is supposed to achieved. It returns a certain table name given its position in the current collation, but I don't see what this could be useful for, neither in general, nor in relation to the problem in your original post. > if @Count = 1 or @Count = 4 or @Count = 7 or @Count = 8 or > @Count >= 14 or [quoted text clipped - 6 lines] > of items at those specific positions in > TestData6061.INFORMATION_SCHEMA.Tables And then someone adds accounting_bck for some reason and your numbers get out of sync? SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Thanks Erland for the reply first of all. The thing is like I didn't > > really design the system. So here is what I came up with so far. I [quoted text clipped - 31 lines] > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx So it seems like I don't really have to look into the Information shcema for TestData6061 or any other tables. This data can be accessed from SchemaFields as below SELECT DISTINCT ObjectClass FROM SchemaFields ORDER BY ObjectClass and this will give me a list something like this -> accounting fundstructures investment party etc Now coming back to the looping, I basically want to have a sql query which will loop through the above list (accounting, investment, etc) giving me the single value at a time as it goes through the loop. Here is what the setup is like just for your information. We have a db releases under which there is a table called [SchemaFields] which gets populated with a perl script. We have another set of tables like 'TestData6061' or 'TestData606' etc where we have some data. Now basically we are going to be running some RDL reports (I am not doing it, thank god) by relating data in values in SchemaFields and 'TestDataxxx' etc. So for running the reports we basically want to pass that 'accounting', 'fundstructures' etc and not hard code it. Does that make sense? I am really sorry if I I didn't make any sense before but I am completely new to this part of SQL, I just know very basic sql Thanks in advance > We have another set of tables like > 'TestData6061' or 'TestData606' etc where we have some data. This sounds badly designed. Assuming that these tables look something like this: [TestData6061] CustomerID | FirstName | LastName | TestResult -----------+-----------+----------+----------- 1 | Winona | Lord | BTN 2 | Billie | Keister | XRC 3 | Daren | Koster | RNC 4 | Valentine | Perkins | FNG 5 | Aleta | Geyer | RWC [TestData6062] CustomerID | FirstName | LastName | TestResult -----------+-----------+----------+----------- 6 | Bronte | Dunlap | SSC 7 | Sinclair | Basmanoff| RRE 8 | Monty | Siegrist | RBN 9 | Suzie | Bailey | NNN 10 | Shantel | Powers | BNM they should be replaced with a single table like this: [TestData] TestID | CustomerID | FirstName | LastName | TestResult -------+------------+-----------+----------+----------- 0601 | 1 | Winona | Lord | BTN 0601 | 2 | Billie | Keister | XRC 0601 | 3 | Daren | Koster | RNC 0601 | 4 | Valentine | Perkins | FNG 0601 | 5 | Aleta | Geyer | RWC 0602 | 6 | Bronte | Dunlap | SSC 0602 | 7 | Sinclair | Basmanoff| RRE 0602 | 8 | Monty | Siegrist | RBN 0602 | 9 | Suzie | Bailey | NNN 0602 | 10 | Shantel | Powers | BNM > So for running the reports we basically want to > pass that 'accounting', 'fundstructures' etc and not hard code it. This is the confusing bit. What sort of procedure do you have that is equally able to operate on 'accounting' and 'fundstructures' and several other things? Unlike TestData0601 and TestData0602, they don't sound like they have similar structures. Are you doing a lot of SELECT * stuff? If so, then that's another thing that may be good to revise. > > We have another set of tables like > > 'TestData6061' or 'TestData606' etc where we have some data. [quoted text clipped - 49 lines] > Are you doing a lot of SELECT * stuff? If so, then that's another thing > that may be good to revise. So basically we have 2 tables as I mentioned 'SchemaFields' which looks like this (gets populated by a perl script which parses some data ) SchemaID Object Object Field Field Field Field Class Type Name Number DataType Requirement 136 accou Accntg Record 1 genString Mandatory nting Param Class eters 136 accou Accntg Record 1 genString Mandatory nting Param Action 2 ub1 Optional eters And then we have our 'TestData6061', TestData606, TestData607 tables where test data is residing. It'll have test cases with actions as 'New', 'Update' and 'Delete'The idea is to map the above 6 columns (other than SchemaID) with the data in the test data bases to find out the coverage for each object and run report which will show a matrix of Object Class, ObjectType and New/Update/Delete (from TestData tables) for these object classes n object types So while running reports we just want a sql query (either plain select statement or a stored/procedure, function etc) which is of the form EXECUTE <StoredProcedure/Function> 'TestData6061', 'accounting', 133 TestData6061 and 133 is obtained from the dropdown list on the report but the main issue is to iterate through all the object classes which is 'accounting', 'fundstructures', 'calendar' etc and thats what the issue is. Doest that make sense? > So basically we have 2 tables as I mentioned 'SchemaFields' which > looks like this (gets populated by a perl script which parses some [quoted text clipped - 27 lines] > is 'accounting', 'fundstructures', 'calendar' etc and thats what the > issue is. Doest that make sense? If I go back to your original query, it appears that all these tables have the columns [01-RecordClass], [02-RecordAction], AutomationType and [Negative Testcase]. So why are not all these in one table where 'accounting' etc is a key? Judging from the table names, I would guess the table are real business tables, but those funny names seems odd in that context. So I guess that what you have in these tables are test data about various functions in some system. But I understand then why there several tables and not one. If you cannot change that design or add any views to alleviate the situation, dynamic SQL is your way out. See this article on my web site for details: http://www.sommarskog.se/dynamic_sql.html. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > So basically we have 2 tables as I mentioned 'SchemaFields' which > looks like this (gets populated by a perl script which parses some [quoted text clipped - 10 lines] > Optional > eters This is unreadable. Suggest you re-post in this style: insert into SchemaFields ( SchemaId, Object, ObjectClass, FieldType, FieldName, FieldNumber, DataType, FieldRequirement ) values ( -- first row's data ) insert into SchemaFields ( SchemaId, Object, ObjectClass, FieldType, FieldName, FieldNumber, DataType, FieldRequirement ) values ( -- second row's data ) > And then we have our 'TestData6061', TestData606, TestData607 tables > where test data is residing. It'll have test cases with actions as [quoted text clipped - 4 lines] > Object Class, ObjectType and New/Update/Delete (from TestData tables) > for these object classes n object types What is the precise layout of these tables? > So while running reports we just want a sql query (either plain select > statement or a stored/procedure, function etc) which is of the form [quoted text clipped - 3 lines] > is 'accounting', 'fundstructures', 'calendar' etc and thats what the > issue is. Doest that make sense? I think so. Instead of requirements implemented directly as constraints (which would make it impossible to insert or update data in a non-compliant fashion), you have requirements based on the contents of SchemaFields (and want to check what portions of the data are non-compliant). If you can clean up the data before it hits this database, then you should switch to constraints; if you intend to store the data in this database before cleaning, then you can't. How often does the contents of SchemaFields change? Would it be simpler to have Perl directly generate SQL code to perform the desired checks, rather than have it generate data which is then parsed by SQL? > > So basically we have 2 tables as I mentioned 'SchemaFields' which > > looks like this (gets populated by a perl script which parses some [quoted text clipped - 59 lines] > the desired checks, rather than have it generate data which is then > parsed by SQL? 1st row - Insert into SchemaFields (SchemaID, ObjectClass,ObjectType,FieldName, FieldDataType,FieldRequirement) Values (136,'accounting','AccountingParameters','RecordClass', 1,'genString','Mandatory') 2nd row - Insert into SchemaFields (SchemaID, ObjectClass,ObjectType,FieldName, FieldDataType,FieldRequirement) Values (136,'accounting','FinancialAccount','RecordAction', 2,'genString','Mandatory') There are many databases like TestData6061, TestData606, TestData607 under which there are tables like 'accounting', 'fundstructures', 'transaction' etc and each of these tables will have 'New', 'Update' and 'Delete' as the record action. So basically idea is to find out the coverage for 'accounting' -> 'AccountingParameters' for 'New', 'Update' and 'Delete' by mapping TestData6061/accounting with accounting and AccountingParamter related rows in SchemaFields So there were some development and now the status is that I have a stored procedure written by my boss which is called as Exec spLoaderCoverageObjectTypeActionType 'TestData6061', 'accounting', 136 The middle argument is dynamic and is taken from the result of SELECT DISTINCT ObjectClass FROM SchemaFields order by ObjectClass > > > So basically we have 2 tables as I mentioned 'SchemaFields' which > > > looks like this (gets populated by a perl script which parses some [quoted text clipped - 88 lines] > FROM SchemaFields > order by ObjectClass As I mentioned in my last post I did some research and quickly wrote a cursor and tried to execute the stored procedure within cursor in a looop. Here is the code DECLARE @ObjectClass VARCHAR(200) DECLARE CursorTemplate CURSOR FAST_FORWARD FOR SELECT DISTINCT ObjectClass FROM releases.dbo.SchemaFields ORDER BY ObjectClass OPEN CursorTemplate FETCH NEXT FROM CursorTemplate INTO @ObjectClass WHILE (@@FETCH_STATUS = 0) BEGIN EXECUTE releases.dbo.spLoaderCoverageObjectTypeActionType 'TestData6061', @ObjectClass, 136 FETCH NEXT FROM CursorTemplate INTO @ObjectClass END CLOSE CursorTemplate DEALLOCATE CursorTemplate ** When I run this in the 'sql pane' for the SchemaFields table I get the New, Update and Delete coverage for accounting but for some reason I don't get others. Ideally it should be giving all as its in a while loop right? > There are many databases like TestData6061, TestData606, TestData607 Okay, you referred to these as tables earlier; I assume you're right now, and were wrong then. > under which there are tables like 'accounting', 'fundstructures', > 'transaction' etc and each of these tables will have 'New', 'Update' > and 'Delete' as the record action. So basically idea is to find out > the coverage for 'accounting' -> 'AccountingParameters' for 'New', > 'Update' and 'Delete' by mapping TestData6061/accounting with > accounting and AccountingParamter related rows in SchemaFields Again, are you intending to keep unclean data out of the database, or allow it into the database so that you can subsequently figure out how you want to clean it up? If the former, then you're re-inventing a square wheel (table constraints). What does the full structure of 'accounting', 'fundstructures', etc. look like? Do they contain a current set of entities, or a list of changes to that set? (Your description of "record action" suggests the latter.) Would it be possible to merge these into a single table with 'accounting', 'fundstructures', etc. as values in an additional column? If not, then how does your per-table logic adjust to account for the different set of columns in each table? > > There are many databases like TestData6061, TestData606, TestData607 > [quoted text clipped - 20 lines] > column? If not, then how does your per-table logic adjust to account > for the different set of columns in each table? what exactly you mean by unclean data? I am not sure about that. basically accounting, fundstructures, transaction contain a set of records which are of type 'New', Update and Delete. Basically I don't really care about the columns that are present in those tables all I am interested in is how many test cases (count) I have for say 'accounting'-> 'AccountingParameters' for 'New', 'Update' and Delete. I want to know the same thing (New, Update and Delete count) for all the object classes (i.e. accounting, calendar, transaction ...) and Object Types (each object class would have a bunch of object types like accounting-> accountingparameters, accounting- >financialaccounting, accounting->lockdownpool) > what exactly you mean by unclean data? I am not sure about that. > basically accounting, fundstructures, transaction contain a set of [quoted text clipped - 7 lines] > like accounting-> accountingparameters, accounting- >>financialaccounting, accounting->lockdownpool) Well, if you don't care, and want to explain to us what you are working, then I start to wonder why we should care. We are asking all these questions to help you to find a good solution, but we never get the information we need. Maybe it makes sense to you to have tables of which the names indicate they are very different entities, but nevertheless have a set of columns in common. But to me that sounds like a very odd thing. I reckon that this is about testing something. But what you test, and why you have these columns, I don't know. > I tried to execute the cursor as mentioned above and it produces the > results I want but they are shown as different data sets in different > tables. I want to run that one exec statement with middle parameter as > dynamic and want to have an output which is a single table. Is that > possible to do in sql? You could insert data into a temp table in that cursor and then select from the temp table in the end. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > what exactly you mean by unclean data? I am not sure about that. > > basically accounting, fundstructures, transaction contain a set of [quoted text clipped - 33 lines] > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx First of all I apologize if I sounded rude or mean or anything like that. I didn't mean it. You guys have been very helpful so far so I really appreciate that. So to answer Ed's question Here is how accounting table looks like (say in TestData6061) insert into accounting (Suite,Testcase,[01-RecordClass],[02- RecordAction],[03-RecordRows],[FinancialAccountCode], [FinancialAccountDesc],[06-AccountNumber]...................) Values (Positive Testcase002 AccountingParameters New 1 Bid_params Bid Ask params USD USD Loader usa Standard NextCalendarDay Do Not Bypass Bid/ Ask on Bonds only GAAP Perspective NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 0 NULL NULL NULL NULL Bid_params 66 66 0 NULL 1 NULL Loader 520) Insert into fundstructures(Suite,TestCase,[01-RecordClass], [02- RecordAction] ........................) Values (NULL FundLegalEntity1 FundLegalEntity New 1 TestFLE Fund Legal Entity added by loader NULL NULL 1 USD 2 Round NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Master NULL TestFLE 1 190 0 NULL 1 1 Loader 520) As mentioned by Ed, yes there are multiple tables which have many identical columns and they have a few extra columns specific to the type (say accounting with have its few unique columns same with fundstructures and other tables in TestData6061). I don't think they can be merged into a single table, I might be wrong, not sure > As mentioned by Ed, yes there are multiple tables which have many > identical columns and they have a few extra columns specific to the > type (say accounting with have its few unique columns same with > fundstructures and other tables in TestData6061). I don't think they > can be merged into a single table, I might be wrong, not sure What may be the best idea is to construct a view over the common columns: CREATE VIEW testcases AS SELECT testcase = 'accounting', [01-RecordClass], [02-RecordAction], ... FROM accounting UNION ALL SELECT 'fundstructures', [01-RecordClass], [02-RecordAction], ... FROM fundstructures UNION ALL ... This presumes that you don't have too many of these tables, as SQL Server only manages 256 tables in one query. You would probably have to define dynamically from that file produced by the Perl script. But from there you could work with the view with static queries. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > As mentioned by Ed, yes there are multiple tables which have many > > identical columns and they have a few extra columns specific to the [quoted text clipped - 26 lines] > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks everyone for helping meon this. I modified the query I mentioned earlier to handle 'transaction' seperately (as it was working for all other object classes) So here is my cursor and the whole sql query which looks like below. I move on to the next task now in the same project so might need some sql help later. Thanks DECLARE @Cmd varchar(5000) DECLARE @ObjectClass VARCHAR(200) DECLARE @ObjectClass1 VARCHAR(200) -- create temp-table to hold the result CREATE table #result (ObjectClass varchar (100), ObjectType varchar(200), ActionType varchar(50), CountNumber int) CREATE table #tranresult (ObjectClass varchar (100), ObjectType varchar(200), ActionType varchar(50), CountNumber int) DECLARE CursorTemplate CURSOR FAST_FORWARD FOR SELECT DISTINCT ObjectClass FROM releases.dbo.SchemaFields where ObjectClass <> 'transaction' ORDER BY ObjectClass OPEN CursorTemplate FETCH NEXT FROM CursorTemplate INTO @ObjectClass1 WHILE (@@FETCH_STATUS = 0) BEGIN Set @Cmd = 'execute releases.dbo.spLoaderCoverageObjectTypeActionType ''TestData6061'''+','+''''+@ObjectClass1+''''+','+'151' print @Cmd INSERT #result EXEC (@Cmd) FETCH NEXT FROM CursorTemplate INTO @ObjectClass1 END CLOSE CursorTemplate DEALLOCATE CursorTemplate insert #tranresult EXECUTE releases.dbo.spLoaderCoverageObjectTypeActionType 'TestData6061','[transaction]' , 151 SELECT * FROM #result union select * from #tranresult where ObjectClass like 'tran%' > > what exactly you mean by unclean data? I am not sure about that. > > basically accounting, fundstructures, transaction contain a set of [quoted text clipped - 33 lines] > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Alright guys finally I hav some success. Here is the cursor code that I came up with which is producing the result I want DECLARE @Cmd varchar(5000) DECLARE @ObjectClass VARCHAR(200) DECLARE @ObjectClass1 VARCHAR(200) -- create temp-table to hold the result CREATE table #result (ObjectClass varchar (100), ObjectType varchar(200), ActionType varchar(50), CountNumber int) DECLARE CursorTemplate CURSOR FAST_FORWARD FOR SELECT DISTINCT ObjectClass FROM releases.dbo.SchemaFields ORDER BY ObjectClass OPEN CursorTemplate FETCH NEXT FROM CursorTemplate INTO @ObjectClass1 WHILE (@@FETCH_STATUS = 0) BEGIN Set @Cmd = 'execute releases.dbo.spLoaderCoverageObjectTypeActionType ''TestData6061'''+','+''''+@ObjectClass1+''''+','+'151' print @Cmd INSERT #result EXEC (@Cmd) FETCH NEXT FROM CursorTemplate INTO @ObjectClass1 END CLOSE CursorTemplate DEALLOCATE CursorTemplate SELECT * FROM #result drop table #result Now the problem is one of the object classes have a name as 'transaction' so sql doesn't like when I am in the while loop executing this line execute releases.dbo.spLoaderCoverageObjectTypeActionType ''TestData6061'''+','+''''+@ObjectClass1+''''+','+'151' when @ObjectClass as 'transaction' which is because its a reserved word in sql. I wonder how I can execute a stored procedure above with middle parameter as 'transaction'? Any feedback would be appreciated. Thanks in advance3 Did you conceder using sp_executesql? MarcM >I have a query which works fine when hardcoded which is as follows > [quoted text clipped - 25 lines] > statement) and 'SchemaID' are all going to be dynamic. Is there anyway > I can generalize this using functions/stored procedures etc > Did you conceder using sp_executesql? > [quoted text clipped - 29 lines] > > statement) and 'SchemaID' are all going to be dynamic. Is there anyway > > I can generalize this using functions/stored procedures etc I tried to execute the cursor as mentioned above and it produces the results I want but they are shown as different data sets in different tables. I want to run that one exec statement with middle parameter as dynamic and want to have an output which is a single table. Is that possible to do in sql? DECLARE @ObjectClass VARCHAR(200) DECLARE CursorTemplate CURSOR FAST_FORWARD FOR SELECT DISTINCT ObjectClass FROM releases.dbo.SchemaFields ORDER BY ObjectClass OPEN CursorTemplate FETCH NEXT FROM CursorTemplate INTO @ObjectClass WHILE (@@FETCH_STATUS = 0) BEGIN EXECUTE releases.dbo.spLoaderCoverageObjectTypeActionType 'TestData6061', @ObjectClass, 136 FETCH NEXT FROM CursorTemplate INTO @ObjectClass END CLOSE CursorTemplate DEALLOCATE CursorTemplate |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.