January 12, 2009 at 11:01 am
I am trying to do an insert to a table via a Stored Pocedure.
I have 2 params one called @appid the other is @val
@appid is a int with a single value.
@val is a comma seperated value.
I have create with some assistance a UDF to deal with the comma Seperated values see code below.
What I am trying to do in t-sql is this
declare @val varchar(8000)
declare @appid int
set @val = ('2212,2194,2215')
set @appid = 14
insert into link (appid, serverid) Values(@appid, (select cast(element as int) from getcommasplit(@val))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetCommaSplit] (@String nvarchar(max))
RETURNS table
AS
RETURN
(
WITH Splitter (Num, String)
AS
(
SELECT Num, SUBSTRING(@String,
Num,
CASE CHARINDEX(N',', @String, Num)
WHEN 0 THEN LEN(@String) - Num + 1
ELSE CHARINDEX(N',', @String, Num) - Num
END ) AS String
FROM dbo.Numbers
WHERE Num <= LEN(@String)
AND (SUBSTRING(@String, Num - 1, 1) = N',' OR Num = 0))
SELECT
ROW_NUMBER() OVER (ORDER BY Num) AS Num,
RTRIM(LTRIM(String)) AS Element
FROM Splitter
WHERE String <> ''
)
January 12, 2009 at 11:19 am
What is the problem that you are having?
January 12, 2009 at 11:21 am
You can't use a sub-query that way in a "Values" statement. You either need to change to Insert...Select, or you need to assign the return value from the function to a variable and use that in the Insert...Values statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 11:39 am
Following up on what Gus said, you want to try it like this:
declare @val varchar(8000)
declare @appid int
set @val = ('2212,2194,2215')
set @appid = 14
insert into link (appid, serverid)
Select @appid, cast(element as int)
from getcommasplit(@val)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply