concatenate a string within a loop from a temp table

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

An easy way (but slow if you have a big table) to do this is to use a cursor. Bellow is a fully functional example.

Hope it helps!

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

DECLARE @.MyResult TABLE (PK Int, SumOfPockets Varchar(4000))

DECLARE @.PK Int

DECLARE @.MyString Varchar(4000)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR

SELECT PK FROM @.MyTable GROUP BY PK

OPEN cMyTable

FETCH NEXT FROM cMyTable INTO @.PK

WHILE @.@.FETCH_STATUS=0

BEGIN

SET @.MyString = ''

SELECT @.MyString = @.MyString + pocket FROM @.MyTable WHERE PK = @.PK

INSERT INTO @.MyResult(PK, SumOfPockets) VALUES (@.PK, @.MyString)

FETCH NEXT FROM cMyTable INTO @.PK

END

CLOSE cMyTable

DEALLOCATE cMyTable

SELECT * FROM @.MyResult

|||

Doru,

This looks like a great solution although i have a very huge table and i ran out of resources when i tried to implement your solution. I was hopping i can do it with some loops and temp tables. do you have any other ideas.

Emad

|||i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.|||

Emadkb wrote:

i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.

If you're running on SQL Server 2005, you can use VARCHAR(MAX) and NVARCHAR(MAX) variables in your string concatenation.

|||we are still on SQL 2000|||

Hi Emad,

If Varchar(8000) is not big enough then you'll have to use Text datatype in your destination table. This will slow down the whole thing even more :-(.

A solution that should work, but which is very slow, is:

---

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

CREATE TABLE #MyResult (PK Int, SumOfPockets Text)
DECLARE @.PK Int, @.pocket Varchar(10), @.prev_PK Int
DECLARE @.ptrColText Varbinary(16)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR
SELECT PK, pocket FROM @.MyTable

OPEN cMyTable
FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.PK = @.prev_PK
UPDATETEXT #MyResult.SumOfPockets @.ptrColText NULL 0 @.pocket
ELSE
BEGIN
INSERT INTO #MyResult(PK, SumOfPockets)
VALUES (@.PK, @.pocket)

SELECT @.ptrColText = TEXTPTR(SumOfPockets)
FROM #MyResult WHERE PK = @.PK

SET @.prev_PK = @.PK
END

FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

END
CLOSE cMyTable
DEALLOCATE cMyTable

SELECT * FROM #MyResult

---

|||

This looks exactly like i want. Excellent work. I will try it and let you know how it goes.

Emad

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
|||Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should[vbcol=seagreen]
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
=
>
|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>
|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still[vbcol=seagreen]
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
this[vbcol=seagreen]
=
>
|||Hi,
temporary tables are always made in tempdb (and only tempdb for your solution)
change
use master
on
use tempdb
Regards,
Pablo
"Steven Yampolsky" wrote:

> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
>
>
|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>
|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>
|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
in
>

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!|||Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
=[vbcol=seagreen]
>|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
this[vbcol=seagreen]
=[vbcol=seagreen]
>|||Hi,
temporary tables are always made in tempdb (and only tempdb for your solutio
n)
change
use master
on
use tempdb
Regards,
Pablo
"Steven Yampolsky" wrote:

> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and stil
l
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
>
>|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
in[vbcol=seagreen]
>|||Won't that function get dropped/cleaned up and will require constant
checking for its existance?
Steve
"Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> Create a permanent function in tempdb, like
> USE tempdb
> GO
>
> CREATE FUNCTION dbo.fnTest
> (@.x int, @.y int)
> RETURNS int
> AS
> BEGIN
> RETURN (@.x+@.y)
> END
> GO
> Now using another database u can call this function during temp table
> creation
> eg.
>
> USE Pubs
>
> CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
>
> INSERT INTO #temp VALUES(1,2)
> INSERT INTO #temp VALUES(3,2)
> SELECT * FROM #temp
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> What
closed?[vbcol=seagreen]
out[vbcol=seagreen]
> wrote
> in
>

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_testGO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO

CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.

I do not get this error if I use a regular table.
HELP!The function you are creating resides in your user database, where as the temporay table is actually created in tempdb database, where your function does not exist.

To test this out I created the function in tempdb and then went into my user database (ex. testdb) and created the temporay table using your script and it worked fine.

The problem with creating a function in tempdb is that the next time SQL Server stops and then restarts the function will be gone. The way around this is to also create the function in model database, since each time SQL Server starts up it takes a copy of model to create tempdb.|||Can you post your script please. I have tried creating the function in the master database and then use it in the temp table creation like this:

z AS (master.dbo.fn_test(x,y))

and still got the error. :confused:|||Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:CREATE TABLE #X
(
x INT,
y INT,
z AS (foo.dbo.fn_test(x,y))
)-PatP|||I did just that and still got the same error. Previous poster mentioned that he was able to do it but haven't posted his version of the script.

Steve

Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:CREATE TABLE #X
(
x INT,
y INT,
z AS (foo.dbo.fn_test(x,y))
)-PatP|||I have a database called Test, where I'm creating the temporay table.

set nocount on
go
use tempdb
go
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO

use test
go
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
go
insert #x values (5,6)
go
select * from #x
go
drop table #xsqlsql

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>|||Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name ='fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
--
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> > I am having a problem with using UDF as part of a temp table computed
> > column. Here's the sample code:
> >
> > IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name
=> > 'fn_test')
> > DROP FUNCTION dbo.fn_test
> > GO
> >
> > CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.z INT
> > SET @.z = @.x + @.y
> > RETURN @.z
> > END
> > GO
> >
> > CREATE TABLE #X
> > (
> > x INT,
> > y INT,
> > z AS (dbo.fn_test(x,y))
> > )
> >
> > I receive the following error:
> >
> > Server: Msg 208, Level 16, State 1, Line 2
> > Invalid object name 'dbo.fn_test'.
> >
> > I do not get this error if I use a regular table.
> > HELP!
> >
> >
>|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name ='fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Looks like the UDF is being looked up in the tempdb database. If you
> created
> > the UDF in tempdb, your table creation will succeed. I am not sure if
this
> > is the expected behavior. I'll post again, if I find out more.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> > "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> > news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> > I am having a problem with using UDF as part of a temp table computed
> > column. Here's the sample code:
> >
> > IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name
=> > 'fn_test')
> > DROP FUNCTION dbo.fn_test
> > GO
> >
> > CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.z INT
> > SET @.z = @.x + @.y
> > RETURN @.z
> > END
> > GO
> >
> > CREATE TABLE #X
> > (
> > x INT,
> > y INT,
> > z AS (dbo.fn_test(x,y))
> > )
> >
> > I receive the following error:
> >
> > Server: Msg 208, Level 16, State 1, Line 2
> > Invalid object name 'dbo.fn_test'.
> >
> > I do not get this error if I use a regular table.
> > HELP!
> >
> >
> >
>|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge! :)
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge! :)
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > As Vyas pointed out, the issue is that you have to create the function
in
> > tempdb. I think he is right in suggesting that you can't use UDFs that
> > reside in a different database in the definition of a table.
> >
>|||Won't that function get dropped/cleaned up and will require constant
checking for its existance?
Steve
"Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> Create a permanent function in tempdb, like
> USE tempdb
> GO
>
> CREATE FUNCTION dbo.fnTest
> (@.x int, @.y int)
> RETURNS int
> AS
> BEGIN
> RETURN (@.x+@.y)
> END
> GO
> Now using another database u can call this function during temp table
> creation
> eg.
>
> USE Pubs
>
> CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
>
> INSERT INTO #temp VALUES(1,2)
> INSERT INTO #temp VALUES(3,2)
> SELECT * FROM #temp
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > How can I create a function in tempdb while inside a stored procedure? I
> > don't think I can do that.
> >
> > Lets assume I can use sp_executesql to create the function inside a SP.
> What
> > will be a lifespan of it? Will it get dropped once the session is
closed?
> > will it be accessible from other sessions? Will there be a name conflict
> > when two sessions will try and execute the same SP?
> >
> > The more I get into it, the more questions I get. Hopefully I'll come
out
> > with good knowledge! :)
> >
> > Steve
> >
> > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > As Vyas pointed out, the issue is that you have to create the function
> in
> > > tempdb. I think he is right in suggesting that you can't use UDFs that
> > > reside in a different database in the definition of a table.
> > >
> >
> >
>|||You can either add the function to the model database (on the basis of which
all databases are created) or you can create a start up stored procedure to
create the function in tempdb whenever SQL Server is started. The function
will not just disappear from tempdb once you have created it, only when SQL
Server is restarted, because tempdb gets recreated then.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:ufdhcNTXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Won't that function get dropped/cleaned up and will require constant
> checking for its existance?
> Steve
> "Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
> news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> > Create a permanent function in tempdb, like
> >
> > USE tempdb
> > GO
> >
> >
> > CREATE FUNCTION dbo.fnTest
> > (@.x int, @.y int)
> > RETURNS int
> > AS
> > BEGIN
> > RETURN (@.x+@.y)
> > END
> > GO
> >
> > Now using another database u can call this function during temp table
> > creation
> >
> > eg.
> >
> >
> > USE Pubs
> >
> >
> > CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
> >
> >
> > INSERT INTO #temp VALUES(1,2)
> > INSERT INTO #temp VALUES(3,2)
> >
> > SELECT * FROM #temp
> >
> >
> >
> > --
> > Roji. P. Thomas
> > SQL Server Programmer
> > "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> > news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > > How can I create a function in tempdb while inside a stored procedure?
I
> > > don't think I can do that.
> > >
> > > Lets assume I can use sp_executesql to create the function inside a
SP.
> > What
> > > will be a lifespan of it? Will it get dropped once the session is
> closed?
> > > will it be accessible from other sessions? Will there be a name
conflict
> > > when two sessions will try and execute the same SP?
> > >
> > > The more I get into it, the more questions I get. Hopefully I'll come
> out
> > > with good knowledge! :)
> > >
> > > Steve
> > >
> > > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> > wrote
> > > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > As Vyas pointed out, the issue is that you have to create the
function
> > in
> > > > tempdb. I think he is right in suggesting that you can't use UDFs
that
> > > > reside in a different database in the definition of a table.
> > > >
> > >
> > >
> >
> >
>