SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pass a table variable column value to a SP output variable


Pass a table variable column value to a SP output variable

Author
Message
VSSGeorge
VSSGeorge
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 1494
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26139 Visits: 17539
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 Modens 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)
VSSGeorge
VSSGeorge
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 1494
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



Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26139 Visits: 17539
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 Modens 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)
VSSGeorge
VSSGeorge
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 1494
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.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14183 Visits: 15963
SELECT @HCodes = DISTINCT...


That should do it, shouldn't it?

John
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26139 Visits: 17539
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 Modens 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)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26139 Visits: 17539
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 Modens 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)
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14183 Visits: 15963
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26139 Visits: 17539
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search