Create table?

Hi All
I want to create a table with a name as a varible. That variable contains the table name by doing some string operations.
say: create table @.var
@.var contains the table name that is generated.
how could i do this?
plz help me .You will need to use dynamic sql to build your CREATE TABLE command, and then EXEC your dynamic sql.|||Why on earth would you ever want to do that? You are opening yourself up for a complete world of hurt. Whatever you are hoping to accomplish with this, you are most certainly solving the problem completely wrong.
Instead of asking fora horrible hack, you need to ask for advise on how to come up with an effective solution.|||Thanks a lot......... it solved my query.

Create table, table name as procedure parameter ?

Hi,

Is it possible to create a table in a stored procedure, where the table name

comes as a string procedure parameter?

Sorry, I am a newbie, maybe it is not possible this way,

but then what is the suggested way?

this results error in SQL Management Studio, if I press Parse.

>Incorrect syntax near '@.tableName'.

the "CREATE TABLE MyFixNameTable" line works, but it fixes the table name.

Code Snippet

CREATE PROCEDURE CreateMyTable

-- Add the parameters for the stored procedure here

@.tableName nvarchar(MAX) = ''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- CREATE TABLE MyFixNameTable

CREATE TABLE @.tableName

(

"^First Name" varchar(25) NOT NULL,

"^Last Name" varchar(25) NOT NULL

)

END

You can't supply an object name as a variable/parameter to a SQL statement.

However, you could create the entire SQL statement as a string, and then use sp_executesql to execute that string.

You may find this article useful:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Like Arnie saie, you cannot create a table like this. Generally speaking, it is rarely a good thing to be programatically creating permanent tables to start with. You can do this with dynamic sql, but why? If you are going to load the data with the results of a query, it is likely best for you to do something like:

select firstName, lastName
into yourTableName
from ...

It is usually faster and avoids some logging overhead. The best way to do this is usually to have a permanent table that includes some other column to denote when you searched for data, etc, some discriminator. Then you can work with the data in the same tables every time you do this, and you code is simplified, and the data is available more readily for reporting what is being done.

Create Procedure Output ntext

Hello,

I need to produce with T-SQL a user defined function or stored
procedure that make one SLQ-Statement and prepare as string from the
result set.
The request muss be able to return a very long unicode string. The
return value nvarchar is being truncated so I'm trying to create a
stored procedure, that returns a ntext string.

I can't manage it (I am no T-SQL specialist). Maybe someone can help
me?

Thanks for your help.

--
Here is my sp:
alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
@.return ntext output)
AS
BEGIN
DECLARE @.onelevel nvarchar(4000)
DECLARE @.pos varchar(1000)
DECLARE @.leveldone varchar(100)
DECLARE @.levelplaned varchar(100)
DECLARE @.planeddate nvarchar(4000)
DECLARE @.elementid varchar(10)
DECLARE @.levelid varchar(10)
DECLARE @.levelstatus varchar(10)
DECLARE @.levelupd nvarchar(4000)
DECLARE @.levelauthor varchar(10)
DECLARE @.prevelementid varchar(10)

BEGIN
declare level_cursor CURSOR FOR
SELECT
B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
convert(varchar,A.Upd,126) as Upd,A.Author
FROM T_TABLE1 as B left outer join T_TABLE2 as A on
(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
where B.ParentID=@.parentid
ORDER BY B.ElementID,B.ElementPos
END

set @.onelevel=''

OPEN level_cursor

FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
@.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor

WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.prevelementid=@.elementid

if (@.pos IS NULL)
set @.onelevel=''
else
set @.onelevel=@.pos

if (@.elementid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.elementid

if (@.levelid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelid

if (@.leveldone IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.leveldone

if (@.levelplaned IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelplaned

if (@.planeddate IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.planeddate

if (@.levelstatus IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelstatus

if (@.levelupd IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelupd

if (@.levelauthor IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelauthor

-- Part Output
print @.onelevel

if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)

FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor

if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor

END

CLOSE level_cursor
DEALLOCATE level_cursor

RETURN

END

--
Call of the function with:
exec dbo.F_FUNCTION 550,1632, 0, ''

Here the beginning of the query analyser output:

1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.

--I think you cannot return that using an output parameter - you'll have to
return it as a field in a SELECT.

On 24 Oct 2005 08:17:01 -0700, rey@.infoman.de wrote:

>Hello,
>I need to produce with T-SQL a user defined function or stored
>procedure that make one SLQ-Statement and prepare as string from the
>result set.
>The request muss be able to return a very long unicode string. The
>return value nvarchar is being truncated so I'm trying to create a
>stored procedure, that returns a ntext string.
>I can't manage it (I am no T-SQL specialist). Maybe someone can help
>me?
>Thanks for your help.
>--
>Here is my sp:
>alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
>@.return ntext output)
>AS
>BEGIN
> DECLARE @.onelevel nvarchar(4000)
> DECLARE @.pos varchar(1000)
> DECLARE @.leveldone varchar(100)
> DECLARE @.levelplaned varchar(100)
> DECLARE @.planeddate nvarchar(4000)
> DECLARE @.elementid varchar(10)
> DECLARE @.levelid varchar(10)
> DECLARE @.levelstatus varchar(10)
> DECLARE @.levelupd nvarchar(4000)
> DECLARE @.levelauthor varchar(10)
> DECLARE @.prevelementid varchar(10)
> BEGIN
> declare level_cursor CURSOR FOR
> SELECT
>B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
>convert(varchar,A.Upd,126) as Upd,A.Author
> FROM T_TABLE1 as B left outer join T_TABLE2 as A on
>(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
> where B.ParentID=@.parentid
> ORDER BY B.ElementID,B.ElementPos
> END
> set @.onelevel=''
> OPEN level_cursor
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
> @.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>set @.prevelementid=@.elementid
> if (@.pos IS NULL)
> set @.onelevel=''
> else
> set @.onelevel=@.pos
> if (@.elementid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.elementid
> if (@.levelid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelid
> if (@.leveldone IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.leveldone
> if (@.levelplaned IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelplaned
> if (@.planeddate IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.planeddate
> if (@.levelstatus IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelstatus
> if (@.levelupd IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelupd
> if (@.levelauthor IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelauthor
> -- Part Output
> print @.onelevel
> if (@.return is NULL)
> exec(@.return+@.onelevel)
> else
> exec(@.return+'*;*'+@.onelevel)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> END
> CLOSE level_cursor
> DEALLOCATE level_cursor
> RETURN
>END
>--
>Call of the function with:
>exec dbo.F_FUNCTION 550,1632, 0, ''
>Here the beginning of the query analyser output:
>1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '*'.
>--|||Am 24 Oct 2005 08:17:01 -0700 schrieb rey@.infoman.de:

...
> --
> Call of the function with:
> exec dbo.F_FUNCTION 550,1632, 0, ''
> Here the beginning of the query analyser output:
> 1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '*'.
> --

ntext is a valid datatype for the OUTPUT variable of a stored procedure.
This is not the problem. Your error appears here:
...
-- Part Output
print @.onelevel

if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)
...
because if @.return is Null then you do a
exec('1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277')
and what should this be?
EXEC starts another stored proc, from there you get your error message. And
so it is an error in line 1.

bye
Helmut|||Hello Helmut,

thanks for your answer.
With exec(@.return+@.onelevel) I try to fill my @.return variable with
the content of @.onelevel. I can't do it with set @.return=@.onelevel
because @.return is of type ntext.

How can I do it else?

thanks and bye,
Agns.|||Not sure if you can do this with ntext, but try this

select @.return = @.return + '*;*' + @.onelevel|||(rey@.infoman.de) writes:
> thanks for your answer.
> With exec(@.return+@.onelevel) I try to fill my @.return variable with
> the content of @.onelevel. I can't do it with set @.return=@.onelevel
> because @.return is of type ntext.
> How can I do it else?

You can't. Since you are in a dead end, I suggest that you explain
your underlying business problem that you are trying to solve. What
does the calling side of this look like?

I can offer one workaround: move to SQL 2005, which offers the
new datatype nvarchar(MAX), which in difference to ntext is a first
class citizen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 25 Oct 2005 00:35:55 -0700, rey@.infoman.de wrote:

>Hello Helmut,
>thanks for your answer.
>With exec(@.return+@.onelevel) I try to fill my @.return variable with
>the content of @.onelevel. I can't do it with set @.return=@.onelevel
>because @.return is of type ntext.
>How can I do it else?
>thanks and bye,
>Agns.

What's wrong with returning it via SELECT rather than via a parameter?

Create procedure or trigger to auto generate string ID

Dear everyone,

I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

Could anyone know how to do that?? Thanks!!

From,

Royi dont think there is any "autogenerated string" you can only have a numeric column...you need to write your own script to get the next value if you decide to use strings ...

hth|||Do not use a string in your DB, rather cast it to a string when you need to use it.|||Thanks you for reply!!

I know there is no autogenerate string. But I need to use string value as primary key. When new record inserted, e.g. string 'A0001' should be generated automatically.

U said that script can help. Could u briefly tell me some steps by how to write??

Thanks for help!!!|||i got some code in my pc at work place...it will take a value and give you the next number/string..

xample:
if you pass 1 to it, it will return 2..
if you pass A1 it will return A2 and return Abcde45 if you pass Abcde44...
the only drawback is it wont work if you try to use a1a...as long as its string followed by number...it works...
i can share it here on monday if you are interested...

hth|||You can write a trigger on that table to fire on INSERT, and that will fill in the next number in your table.

Look up Triggers in Books Online|||Thanks you for reply!!

I could not find books for writing this procedure.

Could u share the code for what u said??

Thanks a lot!!!!!|||Look for SQL Server Books Online - it's a free reference. Do a Google search, I don't know the URL.

Create Extended Procedures in VS 2005

Hi

I am trying to write an extended procedure that accepts a string parameter and returns an integer value. The extended procedure calls a regular stored procedure of a database passing the string parameter as an input. The int value is an OUT parameter to this procedure.

Can I some one suggest where do I get started with respect to this in VS 2005.Why do you want to write an extended stored procedure to call a TSQL SP? This is overkill actually. Extended SPs are meant for computation intensive operations or other logic that cannot be performed efficiently using TSQL. It has it's limitations, performance, reliability and security issues. Or you trying to just learn extended SP programming? If later you can look at the SQL Server samples. You can also look at ODBC/OLEDB samples that will show you how to call SPs.|||The thing is that we need to make DML changes while calling a function. Since normal UDFs dont allow to do it I am trying to call an xp. Since we also need to look at concurrency I am having a stored procedure with transactions taken care. Hence the need of calling T_SQL sp from xp.|||Where do I look for the Extended Stored Procedure DLL Wizard while I open VS 2005 --> Open Project.

I do not see any such wizrd name.

Regards
Imtiaz|||

Use of side-effecting code from UDF is not recommended. It takes lot of work to get it right (dealing with bound connections, concurrency issues, deadlocks, scalability of xps, virtual memory issues depending on how the xp is written etc). Lastly, use of such UDFs in SELECT statement can cause unexpected behavior.