Can a field be append? - 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

Development Tools

Reporting Services Development

Can a field be append?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-19 : 16:23:22

Curious if this is possible. I 2 have really big stored procedure that I want to use with a report in RS. Is it possible to append 2 fields in one table? I know I can insert another detail row, but the problem is that I can only insert First(Fields!Name.Value, "Customer"), and this presents a problem in that is only displays the first records.

In FoxPro, you can select from a query to insert into a temp table, and then append that with another query simply by defining the fields you want to insert it to, and tell it to append. Not sure if RS can do this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 16:27:53

I don't understand what you are asking. Do you just want to concatenate fields? If so use & in Reporting Services or + in T-SQL. If not, please show us an example of what you mean.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-19 : 17:01:02

If possible I was thinking about in T-SQL. I have 2 huge SP that I want to display the value one right after another. These 2 SP are different but share same heading that I want to show in RS. I'm sure it could be done in T-SQL but not sure how.

SP1:

[CODE]
ID Description Qty
123 SA240XKJL22 10
343 TUBE ELBOW 123
332 4" 14WMP 20
[/CODE]

SP2:
[CODE]
ID Description Qty
PO:33 1.0" 16GA 33
PO:22 334 XLLELY 32
PO:21 33L2 LAX 12
[/CODE]

Both combine:
[CODE]
123 SA240XKJL22 10
343 TUBE ELBOW 123
332 4" 14WMP 20
PO:33 1.0" 16GA 33
PO:22 334 XLLELY 32
PO:21 33L2 LAX 12
[/CODE]

Current situation that I'm getting in T-SQL if I try to combine both SP.


ID	Description	Qty	ID	Description	Qty
123	SA240XKJL22	10	PO:33	1.0" 16GA	33
343	TUBE ELBOW	123	PO:22	334 XLLELY	32
332	4" 14WMP 	20	PO:21	33L2 LAX 	12

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 17:05:37

Create a third stored procedure:

CREATE PROC Sproc3
AS

CREATE TABLE #Temp
([ID] int, [Description] varchar(500), Qty int)

INSERT INTO #Temp ([ID], [Description], Qty)
EXEC Sproc1

INSERT INTO #Temp ([ID], [Description], Qty)
EXEC Sproc2

SELECT [ID], [Description], Qty
FROM #Temp

DROP TABLE #Temp

RETURN

GO

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-19 : 17:50:04

Tara,

So it doesn't matter what the field names are as long as it's same number of fields in the SP right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 17:54:46

What matters are that they are compatible data types. And yes same number of columns.

Tara

jhermiz

3564 Posts

Posted - 2005-05-19 : 18:21:44

If they are the same types you can avoid the temp table and union the results...or are they from different tables ?

It looks like one type is character for the ID and the other is int ? Or where you just typing those results ?

Jon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 18:24:23

They are from different stored procedures so you can't union them. You can union the two queries, but it's probably best to just put the stored procedure results into a temp table as the code behind the stored procedure might change and you want this third stored procedure to get those changes without making any sproc3 changes.

Tara

jhermiz

3564 Posts

Posted - 2005-05-19 : 18:27:09

Good call

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-20 : 09:15:58

They are from different tables, and retrieves different data, but for the report aspect, under the same report headers. So yes, SP would be the good choice for this.

Each SP has different number of columns too, but I had add in blank fields in there to make it even out and display each field respective of the other.

For Example:

SP1 has field (Base_ID) while SP2 might be (Part_ID), which are 2 different field, but the user wants it under same Header.

SP1 have 3 fields, while SP2 has 6 fields, so I just use (0 As Blank1) to fill in the first SP to make it has 6 fields.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-20 : 09:33:52

A question about the 3rd SP. I have some parameters for the first 2 SP, and it keeps asking for parameters when I run the third SP. How do I set it so that the parameter gets pass to the first and second SP? I had declare the parameter in the third SP, but it does not seem to be going to them

Example:


CREATE PROCEDURE SP_ITEMS
 @ID VARCHAR(30),
 @PART_ID VARCHAR(30),
 @WAREHOUSE_ID VARCHAR(10)
AS 
CREATE TABLE #TEMP(
	ID SMALLINT,
	PART_ID SMALLINT,
	DESCRIPTION VARCHAR(80),
	QTY DECIMAL DECIMAL(14,4),
	REORDER_POINT DECIMAL(14,4),
	COST DECIMAL(14,4)
	)

INSERT INTO #TEMP(
	ID,
	PART_ID,
	DESCRIPTION,
	QTY DECIMAL
	REORDER_POINT,
	COST
	)
EXEC SP_PARTS

INSERT INTO #TEMP(
	ID,
	PART_ID,
	DESCRIPTION,
	QTY DECIMAL
	REORDER_POINT,
	COST
	)
EXEC SP_WAREHOUSE

SELECT * FROM SP_ITEMS

DROP TABLE #TEMP

So the first 2 SP has the same parameters, how would I pass it to them. When I run the 3rd SP it ask for the parameters, but does not pass it to the 2 others.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-20 : 12:34:54

You need to pass the parameters in the EXEC line. It doesn't know what you intend to do unless you tell it what to do.

EXEC SP_PARTS @parm1 = ...

BTW, never use sp_ for your prefixes for stored procedures. This is a performance hit as SQL Server looks in the master database first to find these.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-20 : 13:46:22

Thanks. Will keep that in mind.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources