October 11, 2013 at 8:13 am
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
October 11, 2013 at 8:38 am
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/[/url]
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2013 at 12:29 am
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
October 15, 2013 at 8:21 am
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.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2013 at 8:18 am
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.
October 16, 2013 at 8:26 am
SELECT @HCodes = DISTINCT...
That should do it, shouldn't it?
John
October 16, 2013 at 8:30 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2013 at 8:33 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2013 at 8:33 am
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
October 16, 2013 at 8:40 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2013 at 12:47 am
If you have understood the problem, please write the code for the solution. I am working for almost a week on this. Thank You.
October 17, 2013 at 7:19 am
Junglee_George (10/17/2013)
If you have understood the problem, please write the code for the solution. I am working for almost a week on this. Thank You.
I can't write the code. I don't have any tables to work with. You would have been done on the 11th (the first day you posted) if you had listened to what I said. The article I have posted about generating a comma separated list explains EXACTLY how to do this. We still haven't seen any ddl or sample data. And it sounds like you basically want us to do your work for you. There are two major issues with that. First around here we like to teach people so they can do these things themselves in the future. Secondly, you are the one getting paid for this not any of us. In fact, we don't get paid at all for what we do on the forums. I can probably write the sql in less than 5 minutes if I had ddl and sample data to work with.
I have tried on this and a couple of your other threads lately to get you to post ddl but you refuse. I'm out.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2013 at 6:25 pm
Junglee_George (10/17/2013)
If you have understood the problem, please write the code for the solution. I am working for almost a week on this. Thank You.
You've been around this forum long enough and have been told how to get this kind of help many times. Please provide the DDL and the data in a readily consumable format. Please read and heed the article at the first link in the "Helpful Links" section of my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply