How to pass value to function in Sql 2008, table has multiple values

Question

Present it is working when the assigned values are static

DECLARE @str varchar(MAX)='45 | 00055 | 9/30/2016 | Vodafone | Randy Singh |Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00'

SELECT * FROM dbo.SUBSTRING_INDEX (@str,'|',7) (Function)

It is not working

DECLARE @str VARCHAR(1000)
SELECT @Str = [temp] FROM TempTable

It is selecting only last row value from table, I need to pass multiple value

How should I assign to @str as sql table resulted value here. Temp(column has many rows).

Function code.

alter FUNCTION dbo.SUBSTRING_INDEX_1
(
  @str NVARCHAR(4000),
  @delim NVARCHAR(1),
  @count INT
)RETURNS @rtnTable TABLE 
(
   HeaderData  NVARCHAR(2000),
   DetailData NVARCHAR(2000)
)
AS
BEGIN
    DECLARE @cnt INT=1;
    DECLARE @subStringPoint INT = 0
    WHILE @cnt <=@count
    BEGIN 
            SET @subStringPoint=CHARINDEX(@delim,@str,@subStringPoint)+1
            SET @cnt=@cnt+1
    END

    INSERT INTO @rtnTable
    SELECT SUBSTRING(@str,0,@subStringPoint-1)  ,SUBSTRING(@str,@subStringPoint+1,LEN(@str)) 
RETURN
END

It is working

DECLARE @str varchar(MAX)='45 | 00055 | 9/30/2016 | Vodafone | Randy Singh |   Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00'
SELECT * FROM dbo.SUBSTRING_INDEX_1 (@str,'|',7)

It is not working

DECLARE @str TABLE (ObjectNames VARCHAR)

INSERT INTO @str
SELECT o.temp FROM OMSOrderTemp o
SELECT * FROM @str

SELECT * FROM dbo.SUBSTRING_INDEX_1(@str, N'|', 7)

You want @str to be a table valued parameter, that is store the results (multiple rows) of a query? I'm confused as to what the ultimate goal is. — S3S, Oct 14 '16 at 18:03

score 0 · Answer 1 · answered Oct 14 '16 at 18:15

0

You need to use a table value parameter...

DECLARE @str TABLE(temp VARCHAR(max))
INSERT INTO @str
SELECT [temp] FROM TempTable

SELECT * FROM @str

Here is an example with test data, showing you the names of all objects using sys.objects...

DECLARE @str TABLE (ObjectNames VARCHAR(4000))

INSERT INTO @str
SELECT o.Name FROM sys.objects o
SELECT * FROM @str

answered Oct 14 '16 at 18:15

S3S

24,809
5
26
45

When I'm passing @str to function it is throwing error as Must declare the scalar variable – Somashekhar Oct 14 '16 at 18:43
did you declare it? Does your function take a table variable? If it doesn't, then of course your function isn't going to work. You didn't post your function code. – S3S Oct 14 '16 at 18:44
And there is another error while passing @str to function/ table is incompatible with nvarchar – Somashekhar Oct 14 '16 at 18:46
your function is expecting a `varchar`.. so you have to pass in a `varchar`. You have to post your function code for this to be addressed. Most likely you ware trying to get all of the rows into a comma or pipe delimited string. You should check out this... http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – S3S Oct 14 '16 at 18:48
You really shouldn't be using that function for this... but the link above that i posted will help you get your results into a pipe delimited format so you can use it if you really want... – S3S Oct 14 '16 at 19:11
I'm using the above function code, just wanted to assign table resulted value to the function parameter, instead of static value. – Somashekhar Oct 14 '16 at 19:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/125746/discussion-between-scsimon-and-somashekhar-kendule). – S3S Oct 14 '16 at 19:23

How to pass value to function in Sql 2008, table has multiple values

1 Answers1