sql server connect: Can I use a table variable in sp_executesql for output -- sql server connect

Re: Can I use a table variable in sp_executesql for output

thejamie
1/31/2005 7:51:05 AM

Andrew,
Thanks for replying.
I am trying to get percentages of a single table for weighting.
If I understand right, then this should work for my first 60 percent of the
table.
DECLARE @PercA varchar(2)
SELECT @PercA='60'
DECLARE @ITEMZ1 TABLE(item_no varchar(30))
SELECT @SQL=N'(INSERT INTO @ITEMZ1 SELECT TOP '+@A+N' PERCENT L.item_no FROM
iminvloc_sql L INNER JOIN imitmidx_sql N ON L.loc = N.loc AND L.item_no =
N.item_no ORDER BY L.avg_cost * L.usage_ytd DESC)'
EXEC sp_executesql @SQL, N'@ITEMZ1 TABLE(item_no varchar(30)) OUTPUT',
@ITEMZ1 OUTPUT
SELECT * FROM @ITEMZ1

I'm missing something. Is it obvious?
Jamie

[quoted text, click to view]

Re: Can I use a table variable in sp_executesql for output

thejamie
1/31/2005 8:45:06 AM

I found something similar - says it works in YUKON only.
http://www.c-sharpcorner.com/Longhorn/Yukon/TopKeywordInYukon.asp
This is what I really want. Is there a way to do this pre-YUKON?

DECLARE @n AS int
SET @n = 60
SELECT TOP(@n)
L.item_no FROM iminvloc_sql L INNER JOIN imitmidx_sql N ON L.loc = N.loc
AND L.item_no = N.item_no
ORDER BY L.avg_cost * L.usage_ytd DESC

[quoted text, click to view]

Re: Can I use a table variable in sp_executesql for output

thejamie
2/7/2005 6:41:07 AM

Suppose the tmptable needed to be created from a string variable fed into the
stored procedure:
The last line below does not work, but follows the same logic. Can a temp
table be created from an EXEC (@SQL) statement?
DECLARE @SQL nvarchar(4000),@BeginPercent tinyint,@EndPercent
tinyint,@SQLLength int,@FieldList varchar(4000),@SQLTmpTbl
nvarchar(4000),@ComputedField varchar(128)
SELECT @SQL=N'SELECT
L.item_no,N.item_desc_1,N.item_desc_2,L.usage_ytd,L.avg_cost,L.inv_class,L.loc,L.prod_cat
FROM iminvloc_sql L INNER JOIN imitmidx_sql N ON L.loc = N.loc AND L.item_no
= N.item_no ORDER BY L.avg_cost * L.usage_ytd DESC'
SELECT @ComputedField=N'L.usage_ytd * L.avg_cost'
DECLARE @SQLSelect nvarchar(4000),@SQLFROM nvarchar(4000)
SELECT @SQLSelect=dbo.instr(@SQL,'FROM')
SELECT @SQLFROM=REPLACE(@SQL,@SQLSelect,'')
SELECT @FieldList=LTRIM(RTRIM(REPLACE(@SQLSelect,'SELECT','')))
SELECT @FieldList=REPLACE(@FieldList,',',' varchar(255),')
SELECT @FieldList=@FieldList+' nvarchar(255)'
SELECT @SQLTmpTbl=N'DECLARE @tmp TABLE('+@Fieldlist+N')'
Print REPLACE(@SQLTmpTbl,'.','_')
exec sp_executesql @SQLTmpTbl

[quoted text, click to view]

INSTR FUNCTION

thejamie
2/7/2005 7:49:03 AM

CREATE FUNCTION dbo.INSTR (@String1 varchar(4000),@Compare varchar(1))
RETURNS nvarchar(4000) AS
BEGIN
DECLARE @RETURNSTRING nvarchar(4000)
IF CHARINDEX(@Compare, @String1)=0
SET @RETURNSTRING =@String1
ELSE
SET @RETURNSTRING= SUBSTRING(@String1, 1, CHARINDEX(@Compare, @String1) -
1)

RETURN @RETURNSTRING

FIELDLIST FUNCTION

thejamie
2/7/2005 7:51:28 AM

CREATE Function FieldList (@TableName varchar(50)) RETURNS nVarchar(4000) AS
BEGIN DECLARE @Field varchar(50),@Fields nvarchar(4000)
DECLARE Fieldnames CURSOR FOR SELECT [name]+CASE [xtype] WHEN 127 THEN ']
BIGINT NOT NULL,'WHEN 56 THEN '] INT NOT NULL,' WHEN 62 THEN '] FLOAT
NULL,' WHEN 104 THEN '] BIT NULL,'ELSE ']
VARCHAR('+convert(varchar(3),[length])+') NULL,' END FROM syscolumns WHERE ID
IN (SELECT ID FROM sysobjects WHERE type='U' AND Name=@TableName)
SELECT @Fields='' OPEN Fieldnames FETCH NEXT FROM Fieldnames INTO @Field
WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Fields= @Fields + N'['+@Field
FETCH NEXT FROM Fieldnames INTO @Field END
CLOSE Fieldnames DEALLOCATE Fieldnames SELECT
@Fields=LEFT(@Fields,LEN(@Fields)-1) SELECT
@Fields=REPLACE(REPLACE(@Fields,') NULL',')NULL'),'] ',']') RETURN @Fields END

FieldList Function

thejamie
2/7/2005 7:55:06 AM

CREATE Function FieldList (@TableName varchar(50)) RETURNS nVarchar(4000) AS
BEGIN DECLARE @Field varchar(50),@Fields nvarchar(4000)
DECLARE Fieldnames CURSOR FOR
SELECT [name]+CASE [xtype] WHEN 127 THEN '] BIGINT NOT NULL,'WHEN 56 THEN
'] INT NOT NULL,' WHEN 62 THEN '] FLOAT NULL,' WHEN 104 THEN '] BIT NULL,'
ELSE '] VARCHAR('+convert(varchar(3),[length])+') NULL,' END
FROM syscolumns WHERE ID IN (SELECT ID FROM sysobjects WHERE type='U' AND
Name=@TableName)
SELECT @Fields='' OPEN Fieldnames FETCH NEXT FROM Fieldnames INTO @Field
WHILE @@FETCH_STATUS = 0
BEGIN SELECT @Fields= @Fields + N'['+@Field FETCH NEXT FROM
Fieldnames INTO @Field END
CLOSE Fieldnames DEALLOCATE Fieldnames SELECT
@Fields=LEFT(@Fields,LEN(@Fields)-1)
SELECT @Fields=REPLACE(REPLACE(@Fields,') NULL',')NULL'),'] ',']') RETURN

Re: Can I use a table variable in sp_executesql for output

Andrew J. Kelly
2/7/2005 12:04:52 PM

First of all that is a table variable and not a temp table. But in either
case it will not work as you expect. The scoping of variables and temp
tables that are created inside dynamic sql are limited to that execution
only. Once they are done the code outside of that dynamic batch will not
see those objects. Only if you create a temp table (not a table var)
outside of the dynamic sql batch will it be usable in the batch and then
again after the batch. Have a look at these:

http://www.aspfaq.com/show.asp?id=2248 Arrays & Lists
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
http://www.users.drew.edu/skass/sql/ListToTableProc.sql.txt List to
Table script

http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: Can I use a table variable in sp_executesql for output

thejamie
2/8/2005 8:53:06 AM

That worked quite well. Thank you Andrew

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server connect group.