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 script without drop

Hello all!
I'm currently working on a project where we have several customers with
the same application. The database is constantly being changed and it's
hard to keep track of all the changes from all the versions in the
customers' systems.
Usually I create the changes script every time I alter any of the tables
but there is always a risk of loosing them. I wonder if there is anyway
of creating a script that updates all the tables instead of dropping and
creating them all, so our customers won't loose the database records.
Thanks in advance,
Hugo MadureiraHugo,
You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
TABLE.
eg.
Alter Table MyTable
Add MyColumn varchar(10)
HTH
Barry|||You can get rid of a lot of headaches by using SQL Compare.
www.red-gate.com
"Hugo Madureira" <hugomadureira@.hotmail.com> wrote in message
news:%232216JpJGHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hello all!
> I'm currently working on a project where we have several customers with
> the same application. The database is constantly being changed and it's
> hard to keep track of all the changes from all the versions in the
> customers' systems.
> Usually I create the changes script every time I alter any of the tables
> but there is always a risk of loosing them. I wonder if there is anyway of
> creating a script that updates all the tables instead of dropping and
> creating them all, so our customers won't loose the database records.
>
> Thanks in advance,
> Hugo Madureira|||Of course, this gets more complex than just adding columns. Such as
adding/removing columns with check constraints, foreign key constraints,
primary key constraints, unique constraints, computed columns, changing
datatypes/scale/precision, etc. Not all table changes are adding columns.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138731694.928227.324210@.z14g2000cwz.googlegroups.com...
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>|||Ahh now I understand what he *actually* wanted... oops!
Barry|||I was looking for a possible way of doing that with Enterprise Manager
manager, in a way that it could be done automatically.
When I use Enterprise Manager to create a table script, it drops the
table and re-creates it. That causes data loss in the database.
If there is no way of doing that, is it possible to easily edit the
script generated by Enterprise Manager to do that?
Barry wrote:
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>

Create Store Procedure to Paging!

Hi All!

I have Store Procedure:

If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum')
Drop Procedure Forum_Topic_SelectFromForum
go
CREATE PROCEDURE Forum_Topic_SelectFromForum
(
@.ForumID varchar(10)
)

AS BEGIN TRANSACTION
SELECT * from Forum_Topic whereForumID=@.ForumID Order by Tmp DESC
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Now, I want to Add 2 Variables: @.Offset int, @.Count int . With @.Offset: the point of data, @.Count: sum of row will get.

when get data I want it get from @.Offset to Added @.Count.

Help me to rewrite this store procedure. Thanks

Hi duynnh,

Are you using SQL 2005? That makes it really easy. Seethis blog post for an example of how you can use a CTE and the new Row_Number() function.

If you're not using 2005 you can do it by creating dynamic SQL. Check outthis post for a generic 'returnpage' stored procedure. Other options include using a temp table or possibly some trickery involving set rowcount if your schema works nicely with that method. Seehere for a more thorough examination of your options.

I hope that helps.

|||

Try the code below (assuming you are using sql 2005/express). I've tested in my side, it works fine

CREATE PROCEDURE Forum_Topic_SelectFromForum( @.ForumID varchar(10), @.offsetint, @.countint)AS BEGIN TRANSACTIONselect * from(SELECT *,row_number() over( Order by Tmp DESC)as row from Forum_Topic where ForumID=@.ForumID )as testwhere test.row between @.offset and @.offset+@.countIF @.@.ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION

Create Store Procedure Fails "Incorrect syntax near the keyword 'ON'."

Hi All!

I'm really new to SQL environment in general so, sorry if this is a stupid question.

I'm trying to create a Stored Procedure on my BD with SQL Server Management Studio Express.

I receive this error:
Msg 156, Level 15, State 1, Procedure sprocBlogEntrySelectListByCategory, Line 18
Incorrect syntax near the keyword 'ON'.

This is the sp:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories ON BlogPosts.bp_ID = PostInCategories.bp_ID INNER JOIN

Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

GO

I really don't understand this error and what does this means in my case.

Any suggestion is appreciated.

alan

It seems to me that the error is in your FROM clause. FROM must be followed with a table name, derived table, or view.

In your case, you have a FROM clause followed by a JOIN condition without the JOIN clause.

|||I am guessing you mean this:

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories

INNER JOIN BlogPosts ON BlogPosts.bp_ID = PostInCategories.bp_ID

INNER JOIN Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

hth.

http://www.elsasoft.org|||

I tryed to re-write the sp in SQL Mgm Studio from scratch.
Identical to that one I posted earlier in my opening, and it was accepted without problem.

I think there was same TAB, SPACE or Comma character wrong.

I think my "issue" is resoved.

Thanks for yuor help anyway

Alan.