• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

Help with stored Proc; Cursor

I have a number of tables that have the same structure, i.e., ID and Name, where ID is the primary key and name is a varchar column.  I want to create a common stored proc that returns these fields, where the differences are the name of the table and the column names.   I'm using a Cursor to read the tables.    So, I'd like to have one proc call another proc -  and either have the calling proc create the cursor and pass it along, or have the calling proc create the SQL and pass it along and then the called proc would create the cursor.  

The called proc does some data manipulation and populates a temp table.

I cannot get either way to work, as I get syntax errors.    Any ideas?
0
HLRosenberger
Asked:
HLRosenberger
  • 8
  • 3
1 Solution
 
ste5anSenior DeveloperCommented:
Posting your code??

Why do you have "a number of tables that have the same structure"? This makes normally no sense. "I want to create a common stored proc" this neither. "where the differences are the name of the table and the column names." you said they have the same structure.. So what is correct now? "I'm using a Cursor to read the tables. " Why that? "So, I'd like to have one proc call another proc -  and either have the calling proc create the cursor.." D'oh?

Please rephrase your question. Post a concise and complete example. This includes table DDL and sample data INSERT statements as a runnable T-SQL script. Also describe your desired output.
0
 
HLRosenbergerAuthor Commented:
the structure of the tables is not really the issue; and I sort of misspoke - they all have an ID and a name columns.  They contain different data, like religion, language, ethnicity, etc.   I need to apply some business logic after reading data from each table, and prior to returning it to user.   So, I find myself doing the same thing over and over - 1) create a cursor, 2 ) fetch, 3) WHILE @@FETCH_STATUS = 0,  4) apply business logic, 5) fetch again, 6) write manipulated data to a temp table 7) SELECT * FROM #tmpTable;

I want to create a proc that does all this - but somehow pass in the SQL statement that is used to create the Cursor (which is different for each table).  Does this make more sense?
0
 
HLRosenbergerAuthor Commented:
if I pass in the SQL as parm (@SQL as varchar(3000), I cannot then do this:

DECLARE cur CURSOR FOR SELECT @SQL
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HLRosenbergerAuthor Commented:
whoops. I mean:

DECLARE cur CURSOR FOR  @SQL
0
 
HLRosenbergerAuthor Commented:
this gives a syntax error.
0
 
ste5anSenior DeveloperCommented:
So, I find myself doing the same thing over and over..
Without concrete example, I don't see how we could help. The only general approach would be a table valued parameter. But this is imho in most cases not optimal.

DECLARE cur CURSOR FOR  @SQL
yup, cause this must be a statement.
0
 
HLRosenbergerAuthor Commented:
DECLARE cur CURSOR FOR  @SQL

Ok, I now understand that his must be a statement, but that being said do you understand conceptually what I want to do with this "statement"?   is there an alternative?

I'm trying to think How to give you an example,  but from my point of view, even if I did, it's the concept that I'm not getting across, that I must not be articulating correct.
0
 
HLRosenbergerAuthor Commented:
How about this - If I have two procs, a caller and a caller, if the caller declares a Cursor, is there a way to pass or reference that cursor within the callee?
0
 
HLRosenbergerAuthor Commented:
So how about this  - I have tables, like language, religion, ethnicity, etc. Each has an ID and a name.   A person can have the attributes - one to many.   My "business" logic in the stored procs take the multiple records and "rotates" the data, so that I get "Spanish", "English", "Hindu", "Caucasian" etc. all in one record per person.  Other entities can also have these attributes, so I need to "rotate" the data like at 30 different places, and it's the same code over and over, and I have to copy/paste.   Other than the fact that the table name and the columns name are different, I do the same thing over and over - I rotate the data.   So, I want one proc to do this, and somehow pass the table name and column names, as well as the column name (my final output) of the temp table.
0
 
ste5anSenior DeveloperCommented:
You don't pass cursors. Well I never needed to do this in T-SQL. From your describtion, your business logic can be applied on a cursor. So why don't simply input the the data in tabular form?

E.g.

CREATE TYPE dbo.KeyValues AS TABLE ( [Key] NVARCHAR(255), [Value] NVARCHAR(255) );
GO

CREATE PROCEDURE dbo.BusinessLogic
	@TVP dbo.KeyValues READONLY
AS
	SET NOCOUNT ON;

	-- Magic here, output result in temp table.
	INSERT INTO #Result ( Result )
		SELECT	COUNT(*)
		FROM	@TVP;
GO

CREATE TABLE #Result ( Result INT);

DECLARE @data1 dbo.KeyValues;
DECLARE @data2 dbo.KeyValues;

INSERT INTO @data1
	SELECT	object_id, name
	FROM	sys.tables;

EXECUTE dbo.BusinessLogic @data1;

INSERT INTO @data2
	SELECT	object_id, name
	FROM	sys.columns;

EXECUTE dbo.BusinessLogic @data2;

SELECT	*
FROM	#Result;
GO

Open in new window

0
 
HLRosenbergerAuthor Commented:
thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now