OUTPUT from dynamic sql - 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 2000 Forums

Transact-SQL (2000)

OUTPUT from dynamic sql

Author

Topic

patterc1
Starting Member

2 Posts

Posted - 2005-11-08 : 11:34:16

I have this sp that I got to produce the dynamic sql that I need to return the data I need but when I execute it it does not output anything. How can I make the output of this sp the result of the @sql query?

ALTER PROC sp_Inventory (

@InvType varchar(50),
@LocationName varchar(50) = '%'

)

AS

DECLARE @sql varchar(1000),
@Attr varchar(20),
@numAttr int,
@i int,
@size int,
@A varchar(5),
@str nvarchar(1000),

SET @numAttr = 20
SET @i = 1
SET @size = 1
SET @Attr = ''

/* Select Inventory Columns */
SET @sql = 'SELECT ls.LocationName, i.InventoryId, i.Allocated, i.Quarantined, i.AddedTime, i.LastModifiedTime, i.Height, i.Width, i.Depth '

/* Get Inventory Attribute alias */
WHILE(@numAttr >= @i)
BEGIN

SET @A = 'A' + CAST(@i AS varchar(2)) + ''
SET @str = 'SELECT @Attr = ' + @A + ' FROM Inv_InventoryTypeDef WHERE InventoryTypeName = ''' + @InvType + ''''
EXECUTE sp_executesql @str, N'@Attr varchar(20) OUTPUT', @Attr OUTPUT

IF(@Attr IS NOT NULL)
BEGIN SELECT @sql = @sql + ', i.A' + CAST(@i AS varchar(2)) + ' AS ' + @Attr + ' ' END

SELECT @i = @i + 1
END

/* Join tables and select location */
SELECT @sql = @sql + 'FROM Inv_Inventory AS i INNER JOIN Inv_LocationStorageDef AS ls ON i.LocationStorageDefId = ls.LocationStorageDefId '
SELECT @sql = @sql + 'INNER JOIN Inv_InventoryTypeDef AS itd ON i.InventoryTypeDefId = itd.InventoryTypeDefId '
SELECT @sql = @sql + 'WHERE ls.LocationName LIKE ''' + @LocationName + ''' AND itd.InventoryTypeName = ''' + @InvType + ''' '
/*SELECT @sql = @sql + 'GROUP BY ls.LocationName '*/

--SELECT @sql as 'TEST'

Exec(@sql)

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 11:46:02

>> How can I make the output of this sp the result of the @sql query?

What do you mean by that?

To a file, to QA Result window?

And can you show some sample data and DDL.

Read the hint link below

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 00:02:27

and why do you want to do this in Dynamic SQL?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources