Generic SP calling - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Generic SP calling

Author

Topic

CSears
Starting Member

39 Posts

Posted - 2011-05-29 : 21:00:36

I have been working on a method for generically calling stored procedures for the purposes of integration.

There is a 3rd party Database that we sometimes work with and they populate data according whatever kind of data they are trying to send. Data processing in our system (database) is handled through different SQL Stored Procedures with different input parameters.

The current method for how the data being set up is that there is a master and child table that contains the data for processing defined as so


HeaderTable
  HeaderSysID    BIGINT
  StoredProcName VARCHAR
  (Other tracking info like date time, whether it's been processed or not etc.)

ChildTable
  ChildSysID      BIGINT
  HeaderSysID     BIGINT
  ParameterValue  VARCHAR
  InputValue      VARCHAR

How I have been handling this so far is with Cursors - ugly and slow I know.

I have one cursor that goes through the HeaderTable and grabs all the unprocessed HeaderTable.HeaderSysIDs. Inside of that cursor is another cursor that constructs the dynamic SQL query for that HeaderSysID using the Stored Procedure Name from the Header Table and Parameter/Input values from the Child table with the matching ChildTable.HeaderSysIDs.

I am looking for ways to make this faster. I anticipate one such way would be to do more logic in my First Cursors select statement so that the nested cursor situation no longer exists and I am left with a single cursor.

Is there anyway to rotate the child table in an intelligent way so that I can either end up with a single result per select statement.

For example a select statement that would return '@' + [CT.ParamValue1] + '=' + [CT.InputValue1] +',' ... , '@' + [CT.ParamValueX] + '=' + [CT.InputValueX]

This would make it very easy to plug into our Stored Procedures. Also if you have other suggestions on the Source Data Table Layout that would make this significantly easier I would appreciate the info so that I can present the alternatives to our partners

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-29 : 22:37:04

This is a very bad design. Designing a dynamic system like this is not only very hard to code but is going to be very slow. If you are going to do things this dynamic, then it should be handled in the application and not in T-SQL.

There is no better solution for us to suggest for what you have described.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-30 : 04:09:39

Allthough I generally agree with Tara on this one there are situations where a design like this could be justified but without knowing the true nature of your system it's hard to tell. I have for example created a dynamic bulk loading system where the system imported hundreds of flat files from a cobol based system on a daily basis. It took quite a while to design but was very simple to maintain and was in fact very efficient as well.

Now regarding the building of your dynamic procedure call; you cn do something like this:

DECLARE @MyProcCall nvarchar(max) = 'EXEC ' + (SELECT StoredProcName FROM HeaderTable WHERE ...)

SELECT @MyProcCall = @MyProcCall + '@' + ParameterValue + ' = ''' + InputValue + ''', '
FROM ChildTable
WHERE ..

--> Remove the last comma in the proc-call:
SET @MyProcCall = LEFT(@MyProcCall, LEN(@MyProcCall) - 2)

PRINT @MyProcCall

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources