Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Pass a table variable column value to a SP output variable Expand / Collapse
Author
Message
Posted Friday, October 11, 2013 8:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 593, Visits: 1,081
How to pass a table variable column value to a SP output variable
I want the values in column v (table variable column) to be assigned to @HCodes(SP output variable)
I have a query like this:

DECLARE @MaterialCode varchar(max)
SELECT @MaterialCode = materialItemCode FROM local_MaterialsItemsContainers MIC
INNER JOIN local_MaterialsItems MI
ON (MIC.materialItemIncId = MI.materialItemIncId AND MIC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)
WHERE charindex(MIC.materialItemContainerCode,'MC-00000030-13-0001')<>0

select @MaterialCode

DECLARE @HCodes nvarchar(MAX)


declare @t table (id int identity(1, 1), v varchar(50))

INSERT @t
SELECT LH.hazardCode from Local_MaterialsItems MI
INNER JOIN Local_MaterialsItemsHazards MIH
on MI.materialItemIncId = MIH.materialItemIncId AND MI.materialItemSqlId = MIH.materialItemSqlId
INNER JOIN Local_Hazards LH
on MIH.hazardIncId = LH.hazardIncId AND MIH.hazardSqlId = LH.hazardSqlId
where charindex(MI.materialItemCode,@MaterialCode)<>0

declare @Numbers table (i int identity(1, 1), j bit)
insert into @Numbers
select top 50 null from master.dbo.syscolumns a, master.dbo.syscolumns b

select distinct left(substring(','+v, i + 1, 50), charindex(',', substring(','+v+',', i + 1, 50))-1) as v
from @t t, @Numbers
where substring(','+v, i, 1) = ','
order by v


Post #1504027
Posted Friday, October 11, 2013 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 13,238, Visits: 12,079
Somewhat difficult to figure out what you are trying to do here. I think maybe you are trying to generate a comma separated list of values from the last query?

Take a look at this article. It explains exactly how to do that using FOR XML.

http://www.sqlservercentral.com/articles/71700/

I would also make a suggestion that you either use a permanent numbers table or generate one using a cte. When you use system tables it can get really slow.

Below is an example of doing this with a cte. Creating your numbers table like this results in 0 reads because it doesn't have to touch any actual tables.

WITH
E1(i) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(i)),
E2(i) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(i) AS
(
SELECT top 50 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)

select distinct left(substring(','+v, i + 1, 50), charindex(',', substring(','+v+',', i + 1, 50))-1) as v
from @t t, cteTally
where substring(','+v, i, 1) = ','
order by v


declare @Numbers table (i int identity(1, 1), j bit)
insert into @Numbers
select top 50 null from master.dbo.syscolumns a, master.dbo.syscolumns b

select distinct left(substring(','+v, i + 1, 50), charindex(',', substring(','+v+',', i + 1, 50))-1) as v
from @t t, @Numbers
where substring(','+v, i, 1) = ','
order by v




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1504041
Posted Tuesday, October 15, 2013 12:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 593, Visits: 1,081
Thank You for that.

But how to return the value in column v (table variable column) to be assigned to @HCodes(SP output variable).

The code presently I having is below:


ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialStockRecordReportHdr]
@MaterialItemContainerCode nvarchar(1000),
@HCodes varchar(max) OUTPUT

AS


DECLARE @MaterialCode varchar(max)
SELECT @MaterialCode = materialItemCode FROM local_MaterialsItemsContainers MIC
INNER JOIN local_MaterialsItems MI
ON (MIC.materialItemIncId = MI.materialItemIncId AND MIC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)
WHERE charindex(MIC.materialItemContainerCode,'MC-00000030-13-0001')<>0


declare @t table (id int identity(1, 1), v varchar(50))

INSERT @t
SELECT LH.hazardCode from Local_MaterialsItems MI
INNER JOIN Local_MaterialsItemsHazards MIH
on MI.materialItemIncId = MIH.materialItemIncId AND MI.materialItemSqlId = MIH.materialItemSqlId
INNER JOIN Local_Hazards LH
on MIH.hazardIncId = LH.hazardIncId AND MIH.hazardSqlId = LH.hazardSqlId
where charindex(MI.materialItemCode,@MaterialCode)<>0

;WITH
E1(i) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(i)),
E2(i) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(i) AS
(
SELECT top 50 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)

select distinct left(substring(','+v, i + 1, 50), charindex(',', substring(','+v+',', i + 1, 50))-1) as v
from @t t, cteTally
where substring(','+v, i, 1) = ','
order by v


Post #1504614
Posted Tuesday, October 15, 2013 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 13,238, Visits: 12,079
I will quote myself here.

Sean Lange (10/11/2013)
Somewhat difficult to figure out what you are trying to do here. I think maybe you are trying to generate a comma separated list of values from the last query?

Take a look at this article. It explains exactly how to do that using FOR XML.

http://www.sqlservercentral.com/articles/71700/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1504784
Posted Wednesday, October 16, 2013 8:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 593, Visits: 1,081
I want to pass the comma separated values to the SSRS report to display there.
That's why I have used @Hcodes for output parameter.
I want to pass the comma separated values in table variable column v to be passed to @Hcodes.
That's my requirement. I have posted my code for your easy understanding.
Post #1505235
Posted Wednesday, October 16, 2013 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 5,306, Visits: 9,686
SELECT @HCodes = DISTINCT...

That should do it, shouldn't it?

John
Post #1505249
Posted Wednesday, October 16, 2013 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 13,238, Visits: 12,079
John Mitchell-245523 (10/16/2013)
SELECT @HCodes = DISTINCT...

That should do it, shouldn't it?

John


That will only return the value of one row, not all rows comma separated.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505255
Posted Wednesday, October 16, 2013 8:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 13,238, Visits: 12,079
Junglee_George (10/16/2013)
I want to pass the comma separated values to the SSRS report to display there.
That's why I have used @Hcodes for output parameter.
I want to pass the comma separated values in table variable column v to be passed to @Hcodes.
That's my requirement. I have posted my code for your easy understanding.


And the only piece left is to read the article I posted twice now. It will show you exactly how to generate the comma separated list from a query. I could cobble something together but there are two issues with that. The first is that I don't have any tables to work with so my code would be at best a close guess. The second issue is that if I write the code for you, you don't learn how to do it. I am trying to teach you how to "fish", not hand you a solution.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505260
Posted Wednesday, October 16, 2013 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 5,306, Visits: 9,686
Ah yes - in my great haste I assumed that your query resulted in the comma-separated list. I can't tell whether the issue here is generating the list, or getting it into the output variable?

John
Post #1505261
Posted Wednesday, October 16, 2013 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 13,238, Visits: 12,079
John Mitchell-245523 (10/16/2013)
Ah yes - in my great haste I assumed that your query resulted in the comma-separated list. I can't tell whether the issue here is generating the list, or getting it into the output variable?

John


A little of both I think.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505272
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse